Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: REPLACE/LOCATE function

    Hi,

    I have two columns in a table. for e.g

    Column1 Column2
    -------- ---------
    abcd 01 abcd 01 xyz
    pqrs 12 0pqrs 12 bcd

    I need to get the strings from Column2 except the strings which are in Column1. Also i need to remove any numeric digits in the resulting string.

    The result should be

    Result
    -------
    xyz
    bcd

    I tried using the Replace and the Locate function in DB2, but it doesn't allow field names to be used instead of string values. Can you please help me find a solution for this using a SQL statement .

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example:

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH test_data(column1 , column2) AS (
    VALUES
      ('abcd 01' , 'abcd 01 xyz')
    , ('pqrs 12' , '0pqrs 12 bcd')
    , ('abcd 03' , '789 03abcd 03 abcd')
    )
    SELECT column1 , column2
         , REPLACE( TRANSLATE( REPLACE(column2 , column1 , '')
                             , ''
                             , '123456789'
                             , '0')
                  , '0' , '')
           AS result_keep_blank
         , REPLACE( TRANSLATE( REPLACE(column2 , column1 , '')
                             , ''
                             , '0123456789')
                  , ' ' , '')
           AS result_remove_blank
      FROM test_data
    ;
    ------------------------------------------------------------------------------
    
    COLUMN1 COLUMN2            RESULT_KEEP_BLANK  RESULT_REMOVE_BLANK
    ------- ------------------ ------------------ -------------------
    abcd 01 abcd 01 xyz         xyz               xyz                
    pqrs 12 0pqrs 12 bcd        bcd               bcd                
    abcd 03 789 03abcd 03 abcd   abcd             abcd               
    
      3 record(s) selected.

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

    Question What's wrong

    I can't understand what kind of problem do you have:

    Code:
    select replace(col2, col1, '') "Result of Replace"
    from
    (select 'abcd 01' col1, 'abcd 01 xyz' col2
    from sysibm.sysdummy1 ) a
    Result:

    Result of Replace
    xyz
    What's wrong ?

    Lenny

Posting Permissions

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