need substring based on delimiter
Hi:
I want to create a string from the first 3 elements of a csv (for example). The csv is longer than 3 elements. Example...
aaa,bbb,ccc,ddd,eee,fff,ggg
I want the string "aaa,bbb,ccc".
Tried splitting this to an array (precursor to appending elements 1,2,3), but failed to be able to reference the elements returned...
stdb_a0=# select string_to_array('aaa,bbb,ccc,ddd,eee,fff,ggg',',');
string_to_array
-------------------------------
{aaa,bbb,ccc,ddd,eee,fff,ggg}
(1 row)
stdb_a0=# select string_to_array('aaa,bbb,ccc,ddd,eee,fff,ggg',',')[1];
ERROR: syntax error at or near "["
If there was a way "position" would return the pos of the 3rd comma, I could use sustring. But I don't think it can :-(
On 08/23/2012 02:30 PM, Gauthier, Dave wrote:
I want to create a string from the first 3 elements of a csv (for
example). The csv is longer than 3 elements. Example...aaa,bbb,ccc,ddd,eee,fff,ggg
I want the string "aaa,bbb,ccc".
select array_to_string
(
(
string_to_array
(
'aaa,bbb,ccc,ddd,eee,fff,ggg',','
)
)[1:3],','
);
array_to_string
-----------------
aaa,bbb,ccc
(1 row)
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support