November 13, 2012 at 11:43 am
I need to run a query in a table to find and replace anywhere where there are sets of random 16 and 17 digit numbers. I need to replace them with either 16 or 17 X's.
Any help would be appreciated!
November 13, 2012 at 11:46 am
J-Rock (11/13/2012)
I need to run a query in a table to find and replace anywhere where there are sets of random 16 and 17 digit numbers. I need to replace them with either 16 or 17 X's.Any help would be appreciated!
What makes it random?
Jared
CE - Microsoft
November 13, 2012 at 11:51 am
I have account numbers within text fields in a table that need to be replaced with X's for security purposes. The account numbers aren't always in the same position as they are in journal fields in our call logging software. Below is an example.
"Customer Billy Bob with account number 1111111111111111 needs to have $2.34 credited to his account."
November 13, 2012 at 11:57 am
J-Rock (11/13/2012)
I have account numbers within text fields in a table that need to be replaced with X's for security purposes. The account numbers aren't always in the same position as they are in journal fields in our call logging software. Below is an example."Customer Billy Bob with account number 1111111111111111 needs to have $2.34 credited to his account."
Is the data type of this field actually TEXT?
Jared
CE - Microsoft
November 13, 2012 at 12:04 pm
Our call logging software has it listed as "Memo" but ultimately it is a text field.
November 13, 2012 at 12:23 pm
Please post ddl for the table.
Jared
CE - Microsoft
November 13, 2012 at 12:28 pm
if you know it's a single table, it's going to look something like this:
you can use the pattern matching , regular expression style of the LIKE statement:
SELECT *
FROM YOURTABLE
WHERE CONVERT(varchar(max),Memo)
--16 digits
LIKE '%[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]%'
OR CONVERT(varchar(max),Memo)
--17 digits
LIKE '%[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][0-9]%'
Lowell
November 13, 2012 at 12:39 pm
the next part, the find and replace gets ugly very quickly.
is it possible that more than one number appears int eh memo field? ie two or three 1/17 digit numbers?
is it possible that the search for numbers are formatted, like with spaces or dashes to make it more human readable?
how many rows appear in the query i provided?
Lowell
November 13, 2012 at 12:41 pm
Lowell (11/13/2012)
if you know it's a single table, it's going to look something like this:you can use the pattern matching , regular expression style of the LIKE statement:
SELECT *
FROM YOURTABLE
WHERE CONVERT(varchar(max),Memo)
--16 digits
LIKE '%[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]%'
OR CONVERT(varchar(max),Memo)
--17 digits
LIKE '%[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][0-9]%'
Don't need the OR for the 17.. The 16 will catch it 🙂 and anything more than 16.
Jared
CE - Microsoft
November 13, 2012 at 12:44 pm
SQLKnowItAll (11/13/2012)
Don't need the OR for the 17.. The 16 will catch it 🙂 and anything more than 16.
doh! thanks, how did i miss that?!
Lowell
November 13, 2012 at 1:10 pm
is it possible that more than one number appears int eh memo field? ie two or three 1/17 digit numbers?
Yes
is it possible that the search for numbers are formatted, like with spaces or dashes to make it more human readable?
They will always be formatted as straight numbers, no spaces or dashes.
how many rows appear in the query i provided?
I actually had to search this in two different tables, 8,530 rows in one and 461 in the other.
November 13, 2012 at 1:20 pm
This might end up being a double post, the first one didn't seem to show up so posting again for good measure.
is it possible that more than one number appears int eh memo field? ie two or three 1/17 digit numbers?
Yes
is it possible that the search for numbers are formatted, like with spaces or dashes to make it more human readable?
All are formatted as strictly numbers with no spaces or dashes.
how many rows appear in the query i provided?
Two tables, one with 8,530 rows and the other with 461.
November 13, 2012 at 1:40 pm
ok, i *think * this works fine.
i'm testing it for shorter 4-number-in-a-row patters for simplicity.
it doesn't quote work when the patter exists multiple times...it does fix one offending string each pass...so if the maximum number of times, is say, 4 offending strings in a given memo, you'd need to run it four times.
it also makes it easier that you are just XX-ing out the strings:
CREATE TABLE SomeTable (id int identity(1,1) not null primary key,
Memo TEXT)
--a handful of fake data
insert into SomeTable (Memo)
SELECT TOP 50
t1.name + ' ' +
substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ ' ' + t1.name + ' '
FROM sys.columns t1
cross join (select x='0123456789') a
--rows affected: my sample = 19 rows
SELECT * FROM SomeTable WHERE PATINDEX('%[0-9][0-9][0-9][0-9]%',CONVERT(varchar(max),memo)) > 0
UPDATE SomeTable
SET Memo = STUFF(CONVERT(varchar(max),memo),PATINDEX('%[0-9][0-9][0-9][0-9]%',CONVERT(varchar(max),memo)),16,'XXXX')
WHERE PATINDEX('%[0-9][0-9][0-9][0-9]%',CONVERT(varchar(max),memo)) > 0
Lowell
November 13, 2012 at 5:29 pm
Thank you very much, I got tied up this afternoon but will give it a shot in the morning and let you know how it goes.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply