Selecting three words from a string

Database
Enthusiast

Selecting three words from a string

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.....
9 REPLIES
Teradata Employee

Re: Selecting three words from a string

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
Enthusiast

Re: Selecting three words from a string

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...
Teradata Employee

Re: Selecting three words from a string

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

Regards,

Adeel
Enthusiast

Re: Selecting three words from a string

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?

Teradata Employee

Re: Selecting three words from a string

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
Enthusiast

Re: Selecting three words from a string

Hi Adeel,

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

Re: Selecting three words from a string

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
Enthusiast

Re: Selecting three words from a string

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

Junior Supporter

Re: Selecting three words from a string

Two ways:

Classic: Recursive query.

New (sort of): Use STRTOK (if there is a max number of 'columns').

HTH.

Cheers.

Carlos.