STRING MANIPULATIONS 1

[cristi22]

Hello All -

Please help with my string ordeals...
Need to separate the alpha:

Alpha =
LastName,FirstName,MiddleName

Data:
SANDERS,JOHNNIE
TORRES,DAISY,C

Results:
LAST_NAME MI FIRST_NAME
SANDERS JOHNNIE
TORES C DAISY

Missing the code for MI & the First should not have the MI

  SUBSTR(alpha,1,CASE                          
                    WHEN POSSTR(alpha,',') = 0   
                      THEN LENGTH(RTRIM(alpha))-2
                    ELSE POSSTR(alpha,',')-1   
                END )as last_name 
,SUBSTR(alpha,CASE                          
                    WHEN POSSTR(alpha,',') = 0   
                      THEN LENGTH(RTRIM(alpha))-2
                      ELSE POSSTR(alpha,',')+1   
                    END,LENGTH(RTRIM(alpha)) )    as first_name

Your help is really appreciated!!!
cristi

 

[blom0344]

No DB2 instance at hand, so this is all theoretical without testing:

The position of the first seperator is:

POSSTR(FIELD,',')

The second one:

LOCATE(',' ,FIELD,(POSSTR(FIELD,',')+1))

These are absolute values (counting from start of the string)

The first substring:

SUBSTR(FIELD,1,POSSTR(FIELD,',')-1)

second substring:

SUBSTR(FIELD,POSSTR(FIELD,',')+1,(LOCATE(',' ,FIELD,(POSSTR(FIELD,',')+1))-POSSTR(FIELD,',')))

third substring:

SUBSTR(FIELD,LOCATE(',' ,FIELD,(POSSTR(FIELD,',')+1)),(LENGTH(FIELD)-LOCATE(',' ,FIELD,(POSSTR(FIELD,',')+1))))

This will probably work (some adjusting) if 2 seperators are present within the string.

If this is not the case then you need to work in the CASE construction like:

CASE WHEN 
LOCATE(',' ,FIELD,(POSSTR(FIELD,',')+1)) = 0 
THEN
<< scenario for just one seperator>>
ELSE
<< scenario for 2 seperators >>
END

Ties Blom

 

[cristi22]

THANKS A LOT FOR YOUR HELP!