STRING MANIPULATIONS 1

[cristi22]

Hello all -
Trying to break the name - i've got some feedback & tried to follow but got lost somewhere in the middle...please help!

alpha = WELCH,NORMAN,R
need to break it into first last & mi fields:
here is what i did

SUBSTR(alpha,1,POSSTR(alpha,',')-1) as last_name

the above code is good

SUBSTR(alpha,POSSTR(alpha,',')+1,(LOCATE(',' ,alpha,(POSSTR(alpha,',')+1))-POSSTR(alpha,','))) as first_name

gives NORMAN with comma

don't have the third one either

 

[cristi22]

the alpah could also be w/o the mi:
KOTARSKI,IRENEUS

, right(alpha , (LENGTH(alpha) - LOCATE(',' ,alpha,(POSSTR(alpha,',')+1)))) as mi

 

[blom0344]

christi,

I posted some possible expressions in the original one on this subject, but perhaps you can work it out if you take a closer look at the SUBSTR/LOCATE functions.

If the expression yields 'NORMAN,' instead of 'NORMAN' then the third argument of the function should be decreased with 1.

The LOCATE function is used to search for the position within a field for a certain part of the string.
You need this , cause you have 2 comma's to deal with.

To get a working scenario for records with both 1 and 2 comma's you can use a CASE construction. There is more then 1 solution that'll work.

Take some time to study the functions from SQL cookbooks and you'll can work it out:

http://mysite.verizon.net/Graeme_Birchall/id1.html

Ties Blom

 

[cristi22]

here is what i came up with .... cleaner soloutions are more than welcomed!

, SUBSTR(alpha,1,POSSTR(alpha,',')-1) as last_name 

, SUBSTR(alpha, POSSTR(alpha,',')+1 ,
case when LOCATE(',' ,alpha ,(POSSTR(alpha,',')+1)) > 0 then
(LOCATE(',' , alpha  ,(POSSTR(alpha,',')+1)) - POSSTR(alpha,',')-1) 
else (LENGTH(alpha) - LOCATE(',' ,alpha,(POSSTR(alpha,',')+1)))  end)   
as first_name
[2thumbsup]

, SUBSTR (alpha, 
	case when LOCATE(',' ,alpha ,(POSSTR(alpha,',')+1)) > 0  then
 		(LOCATE(',' ,alpha,(POSSTR(alpha,',') +1 )) +1 )  end ,
(LENGTH(alpha)-LOCATE(',' ,alpha,(POSSTR(alpha,',')+1)))) as mi