All Forums Database
visakhcr 46 posts Joined 11/07
08 Jun 2008
Selecting three words from a string

HiI have some names like William J Clinton, George W Bush etc in a column. It's a char(25) one.I want to separate the first name, middle name and last name and load them into three different columns...can anyone help please.....

Regards, BB
Adeel Chaudhry 773 posts Joined 04/08
08 Jun 2008

Hello,In case if they are currently in the database and you need to move them to some other table/database, you can write a UDF with two parameters, one the input string and the other the index. e.g.select udfIndexSubString('William J Clinton', 1); => Williamselect udfIndexSubString('William J Clinton', 2); => Jselect udfIndexSubString('William J Clinton', 3); => ClintonRegards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

visakhcr 46 posts Joined 11/07
08 Jun 2008

Hi Adeel..thanks for the suggestion...but i have to implement it using a sel query...i dont have any rights to create udfs or proc....is it possible using substr and position? i tried out some, but it's not working...

Regards,
BB

Adeel Chaudhry 773 posts Joined 04/08
08 Jun 2008

String manipulation is not a plus point of SQL. Try out with a recursive SQL that may be of some help...Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

visakhcr 46 posts Joined 11/07
09 Jun 2008

I wrote the following query..SELECT NA_NAME, SUBSTR(NA_NAME,1,INDEX(NA_NAME,' ')-1) AS NA_FIRST,SUBSTR(NA_NAME,INDEX(NA_NAME,' ')+1,1) AS NA_MIDDLE,SUBSTR(NA_NAME,INDEX(NA_NAME,' ')+2)FROM table_namebut this will create problem when there is no middle name in the input data...it will take the first letter of last name as middle name.....how to avoid this?

Regards,
BB

Adeel Chaudhry 773 posts Joined 04/08
09 Jun 2008

You can try with the CASE statement in second field...checking if the string contains two spaces then get middle-name otherwise just select NULL.HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

visakhcr 46 posts Joined 11/07
09 Jun 2008

Hi Adeel,I am trying the same thing...but somehow, it doesn't seem to work..can u please give the query...

Regards,
BB

visakhcr 46 posts Joined 11/07
09 Jun 2008

this query worked...let me know if there is a better wayselect na_name,trim(substr(na_name,1,index(na_name,' '))) first_name,trim(substr(trim(substr(na_name,index(na_name,' '))),1,index(trim(substr(na_name,index(na_name,' '))),' '))) middle_name,trim(substr(trim(substr(na_name,index(na_name,' '))),index(trim(substr(na_name,index(na_name,' '))),' '))) last_namefrom table_name

Regards,
BB

skrafi 6 posts Joined 10/11
25 Jun 2013

Hi to all,
My TABLE data is ,
 
ABCD > EFGH > IJKLMNOP > QRS > TWX9GG
ABCD > EFGH > XYZ > GH
ABCD > EFGH > GHJK >  I45EDBDGG    
BUT ABOVE DATA I HAVE TO MOVE AS A SAPARATE COLUMN BASED ON ">" DELIMTOR  
LIKE
 
COL1     COL2         COL3         COL4            COL5
ABCD      EFGH       IJKLMNOP    QRS             TWX9GG
ABCD      EFGH        XYZ             GH                SD
ABCD      EFGH        GHJK           I45EDBDGG   
 
How it possible ,it is not a Fixed width .
Any one please help me this scenario
 
Thanks!
--------------
Sk.Md.Rafi
 

skmdrf

CarlosAL 512 posts Joined 04/08
25 Jun 2013

Two ways:
Classic: Recursive query.
New (sort of): Use STRTOK (if there is a max number of 'columns').
HTH.
Cheers.
Carlos.

You must sign in to leave a comment.