InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 16:11:48 GMT20http://www.teradata.com/teradataforum/Topic11751-9-1.aspxthis 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_nameMon, 09 Jun 2008 09:28:43 GMTbonchibujihttp://www.teradata.com/teradataforum/Topic11751-9-1.aspxHi Adeel,I am trying the same thing...but somehow, it doesn't seem to work..can u please give the query...Mon, 09 Jun 2008 02:38:46 GMTbonchibujihttp://www.teradata.com/teradataforum/Topic11751-9-1.aspxYou 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,AdeelMon, 09 Jun 2008 02:03:48 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11751-9-1.aspxI 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?Mon, 09 Jun 2008 01:54:49 GMTbonchibujihttp://www.teradata.com/teradataforum/Topic11751-9-1.aspxString manipulation is not a plus point of SQL. Try out with a recursive SQL that may be of some help...Regards,AdeelSun, 08 Jun 2008 23:51:13 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11751-9-1.aspxHi 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...Sun, 08 Jun 2008 23:46:05 GMTbonchibujihttp://www.teradata.com/teradataforum/Topic11751-9-1.aspxHello,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,AdeelSun, 08 Jun 2008 23:24:50 GMTAdeel.Chaudhryhttp://www.teradata.com/teradataforum/Topic11751-9-1.aspxHiI 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.....Sun, 08 Jun 2008 06:03:51 GMTbonchibuji