Split database string column twice in postgresql

Question

I am using ruby on rails with postgresql. I have a table called "teams" with column "team_name". This "team_name" contains values like "1(Team1)", "1(Team2)", "2(Team1)", etc. Now want to take Team1, Team2, Team3 from those team names using sql query. If I use split_part as follows

   sql = "SELECT SPLIT_PART(team_name,'(',2) FROM teams where id=20"
   result = ActiveRecord::Base.connection.execute(sql).to_a

In the result I am getting

    [{"split_part"=>"Team1)"}]

but I want the following result

    [{"split_part"=>"Team1"}]

how about using SUSBTRING with regex, reference: stackoverflow.com/questions/53636887/… — Lam Phan
– Lam Phan, Commented Apr 7, 2021 at 5:22

Sebastián Palma · Accepted Answer · 2021-04-07 07:45:52Z

1

You can use SUBSTRING, but special characters must be escaped (backslash in this case):

Team
  .select("SUBSTRING(team_name FROM '(Team\\d+)') AS team_name")
  .where(id: id)

edited Apr 7, 2021 at 7:45

answered Apr 7, 2021 at 6:34

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

honey Over a year ago

Thank you but if I use pluck its giving original value instead of substring, anyway I made according changes and its working.

Collectives™ on Stack Overflow

Split database string column twice in postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related