4

We have a table that stores a large string of text and within the text is a debit card number, the string of text is copy and pasted from an email the customer has sent in.

How can I search through the 7000+ records and identify/replace the card numbers from 0000-0000-0000-0000 to XXXX-XXXX-XXXX-0000?

0
6

This can handle one credit card per row. If you suspect there can be more than 1 then just execute the UPDATE multiple times (In the demo I ran it as separate statements but you can use GO X)

declare @mycol varchar(1000) = 'Hello! my name is 0000-0000-0000-0000 Inigo Montoya'

select  stuff(@mycol,patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%',@mycol),14,'xxxx-xxxx-xxxx')

Demo

create table #mytable (id int,mycol varchar(max));

insert into #mytable (id,mycol) values
    (1,'Hello! my name is 1234-2345-3456-4567 Inigo Montoya')
   ,(2,'Please continue, 1234-5678, there is nothing to see')
   ,(3,'the 1st one is 1111-2222-3333-4444 and the 2nd is 2222-3333-4444-5555. That''s it')
;

update  t
set     mycol = stuff(mycol,patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%',mycol),14,'xxxx-xxxx-xxxx')
from    #mytable as t
where   patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%',mycol) > 0
;

(2 row(s) affected)

select * from #mytable;

+----+----------------------------------------------------------------------------------+
| id | mycol                                                                            |
+----+----------------------------------------------------------------------------------+
| 1  | Hello! my name is xxxx-xxxx-xxxx-4567 Inigo Montoya                              |
+----+----------------------------------------------------------------------------------+
| 2  | Please continue, 1234-5678, there is nothing to see                              |
+----+----------------------------------------------------------------------------------+
| 3  | the 1st one is xxxx-xxxx-xxxx-4444 and the 2nd is 2222-3333-4444-5555. That's it |
+----+----------------------------------------------------------------------------------+

update  t
set     mycol = stuff(mycol,patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%',mycol),14,'xxxx-xxxx-xxxx')
from    #mytable as t
where   patindex('%[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%',mycol) > 0
;

(1 row(s) affected)

select * from #mytable;


+----+----------------------------------------------------------------------------------+
| id | mycol                                                                            |
+----+----------------------------------------------------------------------------------+
| 1  | Hello! my name is xxxx-xxxx-xxxx-4567 Inigo Montoya                              |
+----+----------------------------------------------------------------------------------+
| 2  | Please continue, 1234-5678, there is nothing to see                              |
+----+----------------------------------------------------------------------------------+
| 3  | the 1st one is xxxx-xxxx-xxxx-4444 and the 2nd is xxxx-xxxx-xxxx-5555. That's it |
+----+----------------------------------------------------------------------------------+
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.