Re: Need help on SQL

Eric,

You will need to use the substr() function to break the original field into two columns. You can use the instr() function to find occurances of the comma in the string.

            last_name=select substr(nom_concat,1,instr(nom_concat,',') - 1) from employe_table;

If you know that there is always a space after the comma before the first_name, then you can use:

            first_name=select ltrim(substr(nom_concat,instr(nom_concat,',') +2)) from employe_table;

otherwise you can ltrim any potential spaces after the comma:

            first_name=select ltrim(substr(nom_concat,instr(nom_concat,',') +1)) from employe_table;

Regards,
Mike

"Eric Pearson" <eric.pearson_at_ssss.gouv.qc.ca> wrote in message news:4vyH6.52$k95.20033_at_news20.bellglobal.com...
> Hi, i have a field names nom_concat, in it I store the name of employes in
> this format: Pearson, Eric
> it's varchar2(32)
>
> I would like to easily separate the first and last name so I can put them
 in
> two different field named first_name and last_name (both are varchar2(15))
>
> I tried with LTRIM and RTRIM but it doesn't work the way I would like it
 to.
> Is there an SQL function that takes all the characters in a string until
 it
> meets a character that I give it i.e. a comma
>
> thanks
>
> Eric Pearson
> eric.pearson_at_ssss.gouv.qc.ca
>
>
Received on Tue May 01 2001 - 20:26:45 CDT