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
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
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)
