Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2011
    Posts
    7

    Unanswered: First name ,MiddleName,Lastname

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 12-02-11 at 02:27. Reason: Change "being 1 and -1" to "being -1". Add description for iSeries.

  3. #3
    Join Date
    Dec 2011
    Posts
    7

    DB2 Query FirstName Middlename Lastname

    Quote Originally Posted by tonkuma View Post
    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..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.)
    Last edited by tonkuma; 12-02-11 at 11:11. Reason: Add last paragraph("Anyhow, if ...")

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 12-02-11 at 13:33.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ...

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 12-03-11 at 09:20. Reason: Add sentence "But, I couldn't find a reason not to work on DB2 9.1 for LUW."

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may also want to use UDFs(INSTRB or REVERSE) on DB2 before 9.7.

  9. #9
    Join Date
    Dec 2011
    Posts
    7

    DB2 Query FirstName Middlename Lastname

    Quote Originally Posted by tonkuma View Post
    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

  10. #10
    Join Date
    Dec 2011
    Posts
    7

    DB2 Query FirstName Middlename Lastname

    Quote Originally Posted by tonkuma View Post
    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

  11. #11
    Join Date
    Dec 2011
    Posts
    7

    Thumbs up DB2 Query FirstName Middlename Lastname

    Quote Originally Posted by tonkuma View Post
    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

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 12-05-11 at 23:14. Reason: Add the sentence "Briefly looking into ... not to work the query."

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    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
    Last edited by tonkuma; 12-05-11 at 23:22. Reason: Add "By the way, you can try INSTRB and MAX UDFs ...". Add "Could you copy and paste ...?".

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    Last edited by tonkuma; 12-06-11 at 00:59. Reason: Add for Example 3

  15. #15
    Join Date
    Dec 2011
    Posts
    7

    Thumbs up DB2 Query FirstName Middlename Lastname

    Did INSTR and MAX scalar functions worked?



    Hi yes it did work out..


    Thanks,
    satish.p

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •