REPLACE/LOCATE function

Rajath · #1 (**permalink**) 06-21-10, 15:02

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 .

tonkuma · #2 (**permalink**) 06-21-10, 16:01

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.

Lenny77 · #3 (**permalink**) 06-21-10, 16:51

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:

Quote:

Result of Replace
xyz

What's wrong ?

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech