How to split a string in sql query

saraswathis · New User Joined: 06 Sep 2006 Posts: 8

hi
I m having a field in DB2 table of length 22. It consists of a name in 3 parts last name, middle initial, first name. e.g., "JAMES A HARVILLE". I need to split the last name and first name separately in the sql query itself - i.e., in the SELECT statement. Please help me to sort this out.

Thanks in advance.

ashwinreddy · Posted: Thu Apr 26, 2007 12:19 pm

Hi,

If you know the extact length of the First,Middle and last names. I think you can use Sub string function as

SUBSTR(column name,1,8)

we will check for any optimised solution from other members.

Cheers
Ashwin

saraswathis · New User Joined: 06 Sep 2006 Posts: 8

but this can be used only for this string. Each field may have different names where the first part would be of different length. how the query can be generalised. is there any UNSTRING function or truncate. delimiter function to serve the purpose?

thanks in adv.

ashwinreddy · Posted: Thu Apr 26, 2007 12:40 pm

Hi,

My intension was to give you an idea that this can be possible through Substring

full solution as

SELECT substr(Field1,1,3) as First,
substring(Field1,4,3) as Middle,
substring(Field1,8,3) as last
From table name

Hope this will helps.

Cheers
Ashwin

ashwinreddy · Posted: Thu Apr 26, 2007 12:43 pm

Hi,

I haven't tested the query as i don't have access to DB2 in my Project.

Please test it and let me know if it works, i think it can be implemented also as

SELECT substr(Field1,1,3) First,
substr(Field1,4,3) Middle,
substr(Field1,8,3) last
From table name

Cheers
Ashwin

saraswathis · New User Joined: 06 Sep 2006 Posts: 8

Sir,

but i need the string to be separated using spaces. As the names in the column can be different we cannot use the length to splt the last name and first name using the length parameter. the other names in the same column can be as follows:
DENISE A WHITE
CECILIA B HUGHES
BETTY ARLENE HODGES

how can this be solved?

thks in adv.

ashwinreddy · Posted: Thu Apr 26, 2007 1:16 pm

Hi,

If thats the case then before spliting the names we need to detect the spaces between the names.

To do that we can use a Case function but it doesn't look good solution.

Can it be possible through some sub quires, lets hope for a better solution from other members.

Cheers
Ashwin

enrico-sorichetti · Posted: Thu Apr 26, 2007 1:56 pm

You might try to use the POSITION construct to parse the column,
if :
there is only a single blank between words
there is always the same number of words

things might get too complicated for a variable occurrence of those things

<picking on>
since it looks like a flaw in the analysis stage of the project You might want to reconsider some changes to the application structure
... splitting the column in three columns
... adding tree columns with the parsed data
... if the overall architecture allows it consider the use of an "EDITPROC"
<picking off>

dick scherrer · Posted: Thu Apr 26, 2007 7:15 pm

Hello,

You also need to be prepared for names like
JOHN RB SMITH
MARY JENKINS MD
FRED J THOMPSON PHD
MIKE KUMAR JR
and on and on and . . .

From the small set of examples, you see that the names can have many different formats. If you look thru a larger "directory" of the names you need to work with, you may discover most of the formats.

While your request appears rather straightforward, it probably will not be due the variety of ways names are stored.

Is this query going to be inside a program? If it is, (once the rules of the name field construction are known), i believe you will be able to do what you want better in code rather than in a query.