|
|
|
Forum Member
 
Group: Forum Members
Last Login: 8/20/2008 6:49:21 AM
Posts: 26,
Visits: 100
|
|
Hi
I 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.....
bonchibuji
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
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); => William
select udfIndexSubString('William J Clinton', 2); => J
select udfIndexSubString('William J Clinton', 3); => Clinton
Regards,
Adeel
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/20/2008 6:49:21 AM
Posts: 26,
Visits: 100
|
|
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...
bonchibuji
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
String manipulation is not a plus point of SQL. Try out with a recursive SQL that may be of some help...
Regards,
Adeel
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/20/2008 6:49:21 AM
Posts: 26,
Visits: 100
|
|
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_name
but 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?
bonchibuji
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/20/2008 6:49:21 AM
Posts: 26,
Visits: 100
|
|
Hi Adeel,
I am trying the same thing...but somehow, it doesn't seem to work..can u please give the query...
bonchibuji
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/20/2008 6:49:21 AM
Posts: 26,
Visits: 100
|
|
this query worked...let me know if there is a better way
select 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_name
from table_name
bonchibuji
|
|
|
|