Splitting up strings of data
Posted: Tue Mar 13, 2012 3:41 pm
If you need to "split" a string into parts, there is a neat built-in function appropriately named SPLIT_PART which can help you do that.
The function has the syntax: SPLIT_PART ( string, delimiter, field ) where string is the argument string, delimiter is what separates the parts of the string and field is the number of the part you want returned.
Examples:
I can easily extract the street address at position 3:
And I can split a string up into the rows of a query result set:
The function has the syntax: SPLIT_PART ( string, delimiter, field ) where string is the argument string, delimiter is what separates the parts of the string and field is the number of the part you want returned.
Examples:
I can easily extract the street address at position 3:
Code: Select all
dbadmin=> SELECT SPLIT_PART('1|Phil Nickerson|1313 Appleton Street|Crave City|TX|76352', '|', 3) "Street Address";
Street Address
----------------------
1313 Appleton Street
(1 row)
Code: Select all
dbadmin=> SELECT SPLIT_PART('JIM|TOM|PATRICK|PENG|MARK|BRIAN', '|', row_num) "User Names"
dbadmin-> FROM (SELECT ROW_NUMBER() OVER () AS row_num
dbadmin(> FROM tables) row_nums
dbadmin-> WHERE SPLIT_PART('JIM|TOM|PATRICK|PENG|MARK|BRIAN', '|', row_num) <> '';
User Names
------------
JIM
TOM
PATRICK
PENG
MARK
BRIAN
(6 rows)