CODEWITHSUNDEEP
sqlpostgresqlsplit
gshivam63
gshivam63

Reputation: 37

how to split columns in two parts after first

there is a column of type text in postgresql and have data in form

aa.bbb.cc.dddd
ee.fff.gg.hh.iiii
kk.ffg.hjf

I want to split it into two parts after first . as follows:

aa   bbb.cc.dddd
ee   fff.gg.hh.iiii
kk   ffg.hjf

What query can I use to do so?

I have tried 

SELECT split_part(col_name, '.', 1) AS part1,
       split_part(col_name,'.',2) AS part2 
from table_name;

But this is splitting it into aa and bbb

Upvotes: 0

Views: 118

Answers (1)

user330315
user330315

Reputation:

You can use strpos to find the first ., then use substr()

select left(col_name, strpos(col_name, '.') - 1) as part1, 
       substr(col_name, strpos(col_name, '.') + 1) as part2
from table_name

Upvotes: 1

Related Questions