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.
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
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.
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
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.
Hi Adeel,I am trying the same thing...but somehow, it doesn't seem to work..can u please give the query...
Regards,
BB
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
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.....