Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2009
    Posts
    19

    Unanswered: db2 substr locate problem

    hi everyone ,

    i got stuck in a problem

    here it is

    one of the db2 table is storing a String

    like this :

    world/asia/india/100
    world/asia/china/101
    world/europe/argentina/10555
    like this

    no of data is stored here

    here i want to use select statament

    to get the output like this
    100
    101
    10555
    only
    i have used substr +locate function but i am not getting the proper output

    can anyone help me out
    thanks for the help

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    can you post what you tried

    a tip: before parsing append "/" to the input string ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2009
    Posts
    19

    select last no from select statment using rtrim

    yeah sure


    here it is

    select Substr(Substr(Substr(country_no,(LOCATE('/',country_no,1)+1)),(LOCATE('/',Substr(country_no,(LOCATE('/',country_no,1)+1)),1)+1)),(LOCATE('/',Substr(Substr(country_no,(LOCATE('/',country,1)+1)),(LOCATE('/',Substr(country_no,(LOCATE('/',country_no,1)+1)),1)+1)),1)+1)) from world_master fetch first 10 rows only

    this command is working

    but this is the long way to do

    i m trying to do with rtrim
    please help me out

    i think that will be a short command

    thank fro the reply

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Substrs other than outmost are not necessary, bacause LOCATE can use third parameter to specify starting position of search.

    You used one("1") as third parameter for all LOCATE functions.

    Here is a shorter example.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     world_master(country_no) AS (
    VALUES
      'world/asia/india/100'
    , 'world/asia/china/101'
    , 'world/europe/argentina/10555'
    )
    SELECT country_no
         , SUBSTR(country_no , LOCATE('/', country_no, LOCATE('/', country_no, LOCATE('/', country_no) + 1) + 1) + 1 )
      FROM world_master
    ;
    ------------------------------------------------------------------------------
    
    COUNTRY_NO                   2                           
    ---------------------------- ----------------------------
    world/asia/india/100         100                         
    world/asia/china/101         101                         
    world/europe/argentina/10555 10555                       
    
      3 record(s) selected.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    if you are in 9.7, try

    select substr(country_no,locate_in_string(country_no,'/',1,3)) from world_master

    well, haven't tested it myself .. so, expect syntax errors ;-)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    From manual "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1"
    INSTR can be used as a synonym for LOCATE_IN_STRING.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    select substr(country_no,locate_in_string(country_no,'/',1,3)+1) from world_master

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by tonkuma View Post
    select substr(country_no,locate_in_string(country_no,'/',1,3)+1) from world_master
    Cheers Tonkuma!

    i knew I wouldn't get it right ;-)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What if there are more than three /'s? I know that's what was given in the example, but what about accounting for provinces/states? Would it be a better solution in the long run to find the '/numeric'?

    Dave

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Dave, I agree with you to an extent ..

    I guess it will be best to put this in a UDF so that any future changes can be easily done.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Using LOCATE_IN_STRING...

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     world_master(country_no) AS (
    VALUES
      'world/asia/india/100'
    , 'world/asia/china/101'
    , 'world/europe/argentina/10555'
    , 'world/europe/eu/france/702646'
    , 'world/antarctic/50'
    , 'moon/2030'
    )
    SELECT country_no
         , SUBSTR( country_no
                 , LOCATE_IN_STRING( country_no
                                   , '/'
                                   , 1
                                   , LENGTH(country_no)
                                     - LENGTH( REPLACE(country_no, '/', '') )
                                   ) + 1
                 ) next_to_last_slash
      FROM world_master;
    ------------------------------------------------------------------------------
    
    COUNTRY_NO                    NEXT_TO_LAST_SLASH           
    ----------------------------- -----------------------------
    world/asia/india/100          100                          
    world/asia/china/101          101                          
    world/europe/argentina/10555  10555                        
    world/europe/eu/france/702646 702646                       
    world/antarctic/50            50                           
    moon/2030                     2030                         
    
      6 record(s) selected.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This will be better.
    Code:
    SELECT country_no
         , SUBSTR( country_no
                 , LOCATE_IN_STRING( country_no
                                   , '/'
                                   , -1
                                   ) + 1
                 ) next_to_last_slash
      FROM world_master;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1"

    LOCATE_IN_STRING

    ...

    start
    ...
    ...
    If the value of the integer is less than zero, the search begins at LENGTH(source-string) + start + 1
    and continues for each position to the beginning of the string.

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Translate + strip

    Quote Originally Posted by ashrash View Post
    hi everyone ,

    i got stuck in a problem

    here it is

    one of the db2 table is storing a String

    like this :

    world/asia/india/100
    world/asia/china/101
    world/europe/argentina/10555
    like this

    no of data is stored here

    here i want to use select statament

    to get the output like this
    100
    101
    10555
    only
    i have used substr +locate function but i am not getting the proper output

    can anyone help me out
    thanks for the help
    You can TRANSLATE all not numbers in spaces then remove spaces.
    You'll get what do you need.

    Lenny

  15. #15
    Join Date
    Aug 2009
    Posts
    19

    db2 substr and locate

    hi guys,


    thanks for the all replies and suggestions shared....

    locate_in_string as well INSTR is not working in db29.5 and db2 8.2 version so i cannnot use this

    what i m thinking is to read the string from last and seacrh for the first '/' slash

    if found then

    trim from there and display

    because there are possiblites of more than 3 '/' slashes ....

    but i dont know how to write the exaxct command .well i m trying myself ...

    if you guys can help me out finding the exaxt select statment pls do

    thanks

Posting Permissions

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