First name ,MiddleName,Lastname

parvathaneni.sati · #1 (**permalink**) 12-01-11, 17:46

Hi Guyz,

I have a scenario like this ..I have a name

Like ---- Satish Jr. Parvathaneni

Firstname=Satish
Middlename=Jr.
Lastname=Parvathaneni

Can this be accomplished using a quaery..Would be greatful if any one can share their toughts..

Thanks

tonkuma · #2 (**permalink**) 12-01-11, 20:02

What are DB2 version/release and platform OS?

This information is mandatory.
For example:
If you are using latest DB2 version/release on LUW or z/OS,
you may want to use LOCATE_IN_STRING(synonym is INSTR) with optional parameter start being -1.
While, latest DB2 (it must be 7.1) on iSeries doesn't support LOCATE_IN_STRING, by referencing Information Center.

parvathaneni.sati · #3 (**permalink**) 12-02-11, 09:27

Quote:

Originally Posted by tonkuma

What are DB2 version/release and platform OS?

This information is mandatory.
For example:
If you are using latest DB2 version/release on LUW or z/OS,
you may want to use LOCATE_IN_STRING(synonym is INSTR) with optional parameter start being -1.
While, latest DB2 (it must be 7.1) on iSeries doesn't support LOCATE_IN_STRING, by referencing Information Center.

Hi we are using DB2 9.0 Version Operating system is windows XP 5.1

Please let me know if this is OK are do you need anything else.

Your help is appreciated..

tonkuma · #4 (**permalink**) 12-02-11, 10:00

Quote:

we are using DB2 9.0 Version Operating system is windows XP 5.1

Please let me know if this is OK ...

Before asking to someone, please try by yourself.

I think that DB2 9.0 on Windows doesn't support LOCATE_IN_STRING.

Anyhow, if you got error, write the query you executed and the error message(s) you got.
(Try to copy and paste both of the query and the message, rather than type in newly by yourself.)

tonkuma · #5 (**permalink**) 12-02-11, 12:09

I'm sorry to have written some serious semtenses.
I felt that I already supplied an example.

I have no environment of DB2 9.0 on Windows.
So the following examples were tested on DB2 9.7.5 Express-C on Windows/XP.

Please try Example 1 on your DB2.

Example 1:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 test_data(name) AS (
VALUES 'Satish Jr. Parvathaneni'
     , 'Satoh Shiho'
     , 'Julius Zina von Bernstein'
)
SELECT name
     , SUBSTR( name , 1 , LOCATE(' ' , name) - 1 ) AS first_name
     , SUBSTR( name
             , LOCATE(' ' , name) + 1
             , NULLIF(last_blank - LOCATE(' ' , name) , 0) - 1
             )                                     AS middle_name
     , SUBSTR( name , last_blank + 1 )             AS last_name
 FROM  test_data
     , LATERAL
       (SELECT MAX(pos) AS last_blank
         FROM  LATERAL
               (SELECT k1 + k2 AS pos
                 FROM  (VALUES 1, 2, 3, 4, 5, 6, 7, 8
                             , 9,10,11,12,13,14,15,16
                       ) k(k1)
                 INNER JOIN
                       (VALUES  0, 16, 32, 48, 64, 80, 96,112
                             ,128,144,160,176,192,208,224,240
                       ) k(k2)
                   ON  k1 + k2 <= LENGTH(name)
               ) q
         WHERE SUBSTR(name , pos , 1) = ' '
       ) r
;
------------------------------------------------------------------------------

NAME                      FIRST_NAME                MIDDLE_NAME               LAST_NAME                
------------------------- ------------------------- ------------------------- -------------------------
Satish Jr. Parvathaneni   Satish                    Jr.                       Parvathaneni             
Satoh Shiho               Satoh                     -                         Shiho                    
Julius Zina von Bernstein Julius                    Zina von                  Bernstein                

  3 record(s) selected.

Example 2: may not work before DB2 9.7 for LUW or DB2 9.1 on z/OS.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 test_data(name) AS (
VALUES 'Satish Jr. Parvathaneni'
     , 'Satoh Shiho'
     , 'Julius Zina von Bernstein'
)
SELECT name
     , LEFT( name , INSTR(name , ' ' , 1) - 1 )    AS firstname
     , SUBSTR( name
             , INSTR(name , ' ' , 1) + 1
             , MAX(INSTR(name , ' ' , -1) - INSTR(name , ' ' , 1) - 1 , 0)
             )                                     AS middlename
     , SUBSTR( name , INSTR(name , ' ' , -1) + 1 ) AS lastname
 FROM  test_data
;
------------------------------------------------------------------------------

NAME                      FIRSTNAME                 MIDDLENAME                LASTNAME                 
------------------------- ------------------------- ------------------------- -------------------------
Satish Jr. Parvathaneni   Satish                    Jr.                       Parvathaneni             
Satoh Shiho               Satoh                                               Shiho                    
Julius Zina von Bernstein Julius                    Zina von                  Bernstein                

  3 record(s) selected.

tonkuma · #6 (**permalink**) 12-02-11, 12:26

In Example 1,
if middlename was not present,
the query retuned null value(name = 'Satoh Shiho').

If you want to get (zero length) string, try this...

Example 1a:

Code:

...
     , SUBSTR( name
             , LOCATE(' ' , name) + 1
             , CASE last_blank
               WHEN LOCATE(' ' , name) THEN
                    0
               ELSE last_blank - LOCATE(' ' , name) - 1
               END
             )                                   AS middle_name
...

tonkuma · #7 (**permalink**) 12-02-11, 19:57

Another example using recursive-common-table-expression.

Tested on DB2 9.7.5 Express-C on Windows/XP.
But, I couldn't find a reason not to work on DB2 9.1 for LUW.

Example 3:

Code:

------------------------------ Commands Entered ------------------------------
WITH
/****************************************
******* Begin of Sample test data *******
****************************************/
 test_data(id , name) AS (
VALUES ( 1 , 'Satish Jr. Parvathaneni'   )
     , ( 2 , 'Satoh Shiho'               )
     , ( 3 , 'Julius Zina von Bernstein' )
)
/****************************************
*******   End of Sample test data *******
****************************************/
, find_last_blank(name , pos_blank) AS (
SELECT name , LENGTH(name)
 FROM  test_data
UNION ALL
SELECT name
     , pos_blank - 1
 FROM  find_last_blank
 WHERE pos_blank > 0
   AND SUBSTR(name , pos_blank , 1) <> ''
) 
SELECT t.name
     , SUBSTR( t.name , 1 , LOCATE(' ' , t.name) - 1 ) AS first_name
     , SUBSTR( t.name
             , LOCATE(' ' , t.name) + 1
             , COALESCE( NULLIF( last_blank - LOCATE(' ' , t.name) - 1
                               , - 1 )
                       , 0 )
           /*  Equivalent to
               CASE last_blank
               WHEN LOCATE(' ' , t.name) THEN
                    0
               ELSE last_blank - LOCATE(' ' , t.name) - 1
               END
           */
             )                                         AS middle_name
     , SUBSTR( t.name , last_blank + 1 )               AS last_name
 FROM  test_data AS t
 INNER JOIN
       (SELECT name
             , MIN(pos_blank) AS last_blank
         FROM  find_last_blank
         GROUP BY
               name
       )         AS f
   ON  f.name = t.name
 ORDER BY
       id
;
------------------------------------------------------------------------------

NAME                      FIRST_NAME                MIDDLE_NAME               LAST_NAME                
------------------------- ------------------------- ------------------------- -------------------------
Satish Jr. Parvathaneni   Satish                    Jr.                       Parvathaneni             
Satoh Shiho               Satoh                                               Shiho                    
Julius Zina von Bernstein Julius                    Zina von                  Bernstein                

  3 record(s) selected.

tonkuma · #8 (**permalink**) 12-02-11, 21:46

You may also want to use UDFs(INSTRB or REVERSE) on DB2 before 9.7.

parvathaneni.sati · #9 (**permalink**) 12-05-11, 12:14

Quote:

Originally Posted by tonkuma

You may also want to use UDFs(INSTRB or REVERSE) on DB2 before 9.7.

Thanks a lot tonkuma You provided me lot of examples..

Really appreciate your Time..

I will try all of them and will let u know the results..

Once again thanks a lot ..You are really helpful...

Regards,
satish.p

parvathaneni.sati · #10 (**permalink**) 12-05-11, 12:27

Quote:

Originally Posted by tonkuma

In Example 1,
if middlename was not present,
the query retuned null value(name = 'Satoh Shiho').

If you want to get (zero length) string, try this...

Example 1a:

Code:

...
     , SUBSTR( name
             , LOCATE(' ' , name) + 1
             , CASE last_blank
               WHEN LOCATE(' ' , name) THEN
                    0
               ELSE last_blank - LOCATE(' ' , name) - 1
               END
             )                                   AS middle_name
...

My DB2 Instace is not accepting the last_blank

Below is the error :sql0206n

"Last_Blank" is not valid in the context where it is used. SQLSTATE=42703

parvathaneni.sati · #11 (**permalink**) 12-05-11, 12:42

Quote:

Originally Posted by tonkuma

I'm sorry to have written some serious semtenses.
I felt that I already supplied an example.

I have no environment of DB2 9.0 on Windows.
So the following examples were tested on DB2 9.7.5 Express-C on Windows/XP.

Please try Example 1 on your DB2.

Example 1:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 test_data(name) AS (
VALUES 'Satish Jr. Parvathaneni'
     , 'Satoh Shiho'
     , 'Julius Zina von Bernstein'
)
SELECT name
     , SUBSTR( name , 1 , LOCATE(' ' , name) - 1 ) AS first_name
     , SUBSTR( name
             , LOCATE(' ' , name) + 1
             , NULLIF(last_blank - LOCATE(' ' , name) , 0) - 1
             )                                     AS middle_name
     , SUBSTR( name , last_blank + 1 )             AS last_name
 FROM  test_data
     , LATERAL
       (SELECT MAX(pos) AS last_blank
         FROM  LATERAL
               (SELECT k1 + k2 AS pos
                 FROM  (VALUES 1, 2, 3, 4, 5, 6, 7, 8
                             , 9,10,11,12,13,14,15,16
                       ) k(k1)
                 INNER JOIN
                       (VALUES  0, 16, 32, 48, 64, 80, 96,112
                             ,128,144,160,176,192,208,224,240
                       ) k(k2)
                   ON  k1 + k2 <= LENGTH(name)
               ) q
         WHERE SUBSTR(name , pos , 1) = ' '
       ) r
;
------------------------------------------------------------------------------

NAME                      FIRST_NAME                MIDDLE_NAME               LAST_NAME                
------------------------- ------------------------- ------------------------- -------------------------
Satish Jr. Parvathaneni   Satish                    Jr.                       Parvathaneni             
Satoh Shiho               Satoh                     -                         Shiho                    
Julius Zina von Bernstein Julius                    Zina von                  Bernstein                

  3 record(s) selected.

Example 2: may not work before DB2 9.7 for LUW or DB2 9.1 on z/OS.

Code:

------------------------------ Commands Entered ------------------------------
WITH
 test_data(name) AS (
VALUES 'Satish Jr. Parvathaneni'
     , 'Satoh Shiho'
     , 'Julius Zina von Bernstein'
)
SELECT name
     , LEFT( name , INSTR(name , ' ' , 1) - 1 )    AS firstname
     , SUBSTR( name
             , INSTR(name , ' ' , 1) + 1
             , MAX(INSTR(name , ' ' , -1) - INSTR(name , ' ' , 1) - 1 , 0)
             )                                     AS middlename
     , SUBSTR( name , INSTR(name , ' ' , -1) + 1 ) AS lastname
 FROM  test_data
;
------------------------------------------------------------------------------

NAME                      FIRSTNAME                 MIDDLENAME                LASTNAME                 
------------------------- ------------------------- ------------------------- -------------------------
Satish Jr. Parvathaneni   Satish                    Jr.                       Parvathaneni             
Satoh Shiho               Satoh                                               Shiho                    
Julius Zina von Bernstein Julius                    Zina von                  Bernstein                

  3 record(s) selected.

- - -
SELECT Full_Name , LEFT( Full_Name, INSTR(Full_Name, ' ' , 1) - 1 ) AS firstname
FROM Details

- - -

SELECT Full_Name, SUBSTR( Full_Name , INSTR(Full_Name , ' ' , 1) + 1
, MAX(INSTR(Full_Name , ' ' , -1) - INSTR(Full_Name , ' ' , 1) - 1 , 0) ) AS middlename
FROM Details

- - -
SELECT Full_Name, SUBSTR( Full_Name, INSTR(Full_Name , ' ' , -1) + 1 ) AS lastname
FROM Details

Hi ,

I have Splitted the Query you have sent to me..

FirstName Works Absoluetly Fine..

2nd Query Which is the middle name is Is giving Me

I.g Actual O.P = Jr.

Query Result = Jr. Parvathaneni

It is giving me the last Name as well..

3rd Query Is not working

Error is --SQL0138N The second arguement of the substr function is out of range SQLSTATE=22011

Please share me your toughts on this.!!

Thanks a lot..

Regards,
satish.p

tonkuma · #12 (**permalink**) 12-05-11, 12:52

Quote:

My DB2 Instace is not accepting the last_blank

Below is the error :sql0206n

"Last_Blank" is not valid in the context where it is used. SQLSTATE=42703

Could you copy and paste your executed query?

It worked on my DB2 9.7 on Windows.
Briefly looking into DB2 8.2 SQL Reference Volume 1,
I couldn't find any reason no to work the query with changing LATERAL to TABLE.
(DB2 9 for LUW supports LATERAL keyword.)

Example 1b:

Code:

------------------------------ Commands Entered ------------------------------
WITH
/****************************************
******* Begin of Sample test data *******
****************************************/
 test_data(id , name) AS (
VALUES ( 1 , 'Satish Jr. Parvathaneni'   )
     , ( 2 , 'Satoh Shiho'               )
     , ( 3 , 'Julius Zina von Bernstein' )
)
/****************************************
*******   End of Sample test data *******
****************************************/
SELECT name
     , SUBSTR( name , 1 , LOCATE(' ' , name) - 1 ) AS first_name
     , SUBSTR( name
             , LOCATE(' ' , name) + 1
             , CASE last_blank
               WHEN LOCATE(' ' , name) THEN
                    0
               ELSE last_blank - LOCATE(' ' , name) - 1
               END
             )                                     AS middle_name
     , SUBSTR( name , last_blank + 1 )             AS last_name
 FROM  test_data
     , LATERAL
       (SELECT MAX(pos) AS last_blank
         FROM  LATERAL
               (SELECT k1 + k2 AS pos
                 FROM  (VALUES 1, 2, 3, 4, 5, 6, 7, 8
                             , 9,10,11,12,13,14,15,16
                       ) k(k1)
                 INNER JOIN
                       (VALUES  0, 16, 32, 48, 64, 80, 96,112
                             ,128,144,160,176,192,208,224,240
                       ) k(k2)
                   ON  k1 + k2 <= LENGTH(name)
               ) q
         WHERE SUBSTR(name , pos , 1) = ' '
       ) r
;
------------------------------------------------------------------------------

NAME                      FIRST_NAME                MIDDLE_NAME               LAST_NAME                
------------------------- ------------------------- ------------------------- -------------------------
Satish Jr. Parvathaneni   Satish                    Jr.                       Parvathaneni             
Satoh Shiho               Satoh                                               Shiho                    
Julius Zina von Bernstein Julius                    Zina von                  Bernstein                

  3 record(s) selected.

tonkuma · #13 (**permalink**) 12-05-11, 13:10

Quote:

SELECT Full_Name, SUBSTR( Full_Name , INSTR(Full_Name , ' ' , 1) + 1
, MAX(INSTR(Full_Name , ' ' , -1) - INSTR(Full_Name , ' ' , 1) - 1 , 0) ) AS middlename
FROM Details

- - -
SELECT Full_Name, SUBSTR( Full_Name, INSTR(Full_Name , ' ' , -1) + 1 ) AS lastname
FROM Details

Hi ,

I have Splitted the Query you have sent to me..

FirstName Works Absoluetly Fine..

2nd Query Which is the middle name is Is giving Me

I.g Actual O.P = Jr.

Query Result = Jr. Parvathaneni

It is giving me the last Name as well..

3rd Query Is not working

Error is --SQL0138N The second arguement of the substr function is out of range SQLSTATE=22011

Did INSTR and MAX scalar functions worked?

You wrote

Quote:

we are using DB2 9.0 Version Operating system is windows XP 5.1

But I couldn't find INSTR nor MAX scalar function in the manual "DB2 Version 9 for Linux, UNIX, and Windows SQL Reference Volume 1".

Could you copy and paste your executed query and error message(s) you got?

By the way, you can try INSTRB and MAX UDFs instead of INSTR and MAX (scalar) built-in functions on DB2 before 9.7.
Sample UDFs for Migration

tonkuma · #14 (**permalink**) 12-05-11, 22:32

Quote:

2nd Query Which is the middle name is Is giving Me

I.g Actual O.P = Jr.

Query Result = Jr. Parvathaneni

It is giving me the last Name as well..

3rd Query Is not working

Error is --SQL0138N The second arguement of the substr function is out of range SQLSTATE=22011

A reason I could guess was that your Full_Name include trailing blank(s).
If so, try to replace Full_Name with RTRIM(Full_Name) in
Example 1: LENGTH(name)
Example 2: INSTR(name , ' ' , -1) -- two included
Example 3: LENGTH(name)

parvathaneni.sati · #15 (**permalink**) 12-08-11, 13:33

Did INSTR and MAX scalar functions worked?

Hi yes it did work out..

Thanks,
satish.p

Internet Services

Web Development

Digital Marketing

Consumer Tech