Thread: need substring based on delimiter

need substring based on delimiter

From

"Gauthier, Dave"

Date:

24 August 2012, 00:31:16

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 :-(

Re: need substring based on delimiter

From

Joe Conway

Date:

24 August 2012, 00:58:58

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