CODEWITHSUNDEEP
sqlsql-server
dopplesoldner
dopplesoldner

Reputation: 9479

SQL multiple REPLACE cases

I have a transaction reference table with varchar Transaction Identifiers in a MSSQL DB.

eg. 20121018A436712SF2CPMQ7177

But in some transactions, there is some noise in the form of special identifiers such as

1114
1160
H600
....
....
etc

I was thinking of using REPLACE statement to replace them like

select REPLACE (identifier, '%1114%', '') from Transactions

but I don't know how to do REPLACE using all these conditions.

Any help will be much appreciated. Thanks

Upvotes: 0

Views: 163

Answers (2)

Kaf
Kaf

Reputation: 33839

If you Transaction identifier is a fixed length string, say 12 characters, you can select the right most 12 characters as;

SELECT RIGHT(identifier_column,12)

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

I would build a table of "special identifiers" and populate it, e.g.

create table special_identifiers (
   id int identity not null primary key clustered,
   key varchar(10) not null unique
);

You would then perform your removal of these special ids like this in a SELECT

    SELECT i.col1, i.col2,
           CASE WHEN si.key IS NOT NULL then '' ELSE i.identifier END identifier
      FROM Transactions i
 LEFT JOIN special_identifiers si on si.key = i.identifier

This can be easily expanded if you really need to use a LIKE, such as 'xxx' anywhere in an id should remove it, e.g.

 LEFT JOIN special_identifiers si on i.identifier LIKE '%' + si.key + '%'

Although I would just add the %'s into the key column itself for greater flexibility.

Finally, if you simply cannot persist the table, you can always virtually make it up, e.g.

    SELECT i.col1, i.col2,
           CASE WHEN si.key IS NOT NULL then '' ELSE i.identifier END identifier
      FROM Transactions i
 LEFT JOIN (select '1114' key UNION ALL
            select '1160') si on si.key = i.identifier

Upvotes: 1

Related Questions