HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

TSQL - How to strip out debit card numbers from a string of text?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlsql-servert-sqlstackoverflowdba
fromtsqlnumbersdebittextcardhowstripstringout

Problem

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?

Solution

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

Code Snippets

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')
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
;
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
;
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 |
+----+----------------------------------------------------------------------------------+

Context

StackExchange Database Administrators Q#164394, answer score: 6

Revisions (0)

No revisions yet.