TSQL – How to Strip Out Debit Card Numbers from Text

sql servert-sql

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?

Best Answer

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

Related Solutions

Sql-server – Replace cursor with set-based approach

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

SQL Server – How to Strip Non-Numeric Characters Out of a String

I found this T-SQL function on SO that works to remove non-numeric characters from a string.

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

Best Answer

Related Solutions

Sql-server – Replace cursor with set-based approach

SQL Server – How to Strip Non-Numeric Characters Out of a String

Related Question