Search for specific length string in column

[mike]

SQL 2000.

I need a query that will search a field in the table that is 14
characters long and begins with a number.

I have a client that was allowing people to store credit card numbers,
plain text, in an application. I need to rip through the table and
replace every instance of credit card numbers with "x" and the last 4
digits. I got the replace bit going, but I am stuck on how to search
for a string in a field of a specific length.

Any ideas?

Thanks,
--Mike

[Jack Vamvas]

select * from MyTable where len(reference) = 2 and
SUBSTRING (reference ,1 ,1 ) IN ('0','1','2','3 ','4','5','6',' 7','8','9')

you could expand and use the ISNUMERIC()
--

Jack Vamvas
_______________ _______________ _____
Need an IT job? http://www.ITjobfeed.com


<mi**@mcarlson. netwrote in message
news:11******** *************@1 9g2000hsx.googl egroups.com...

SQL 2000.

I need a query that will search a field in the table that is 14
characters long and begins with a number.

I have a client that was allowing people to store credit card numbers,
plain text, in an application. I need to rip through the table and
replace every instance of credit card numbers with "x" and the last 4
digits. I got the replace bit going, but I am stuck on how to search
for a string in a field of a specific length.

Any ideas?

Thanks,
--Mike

[Hugo Kornelis]

On Fri, 14 Sep 2007 07:19:25 -0700, mi**@mcarlson.n et wrote:

>SQL 2000.

I need a query that will search a field in the table that is 14
characters long and begins with a number.

Hi Mike,

WHERE LEN(YourColumn) = 14
AND LEFT(YourColumn , 1) LIKE '[0-9]'

>I have a client that was allowing people to store credit card numbers,
plain text, in an application. I need to rip through the table and
replace every instance of credit card numbers with "x" and the last 4
digits. I got the replace bit going, but I am stuck on how to search
for a string in a field of a specific length.

Are you sure you need to check only the first character for numeric? All
my credit cards have only numbers. To test for length 14 and only
numbers, you can change the above to

WHERE LEN(YourColumn) = 14
AND YourColumn NOT LIKE '%[^0-9]%'

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

Hugo Kornelis (hu**@perFact.R EMOVETHIS.info. INVALID) writes:

Are you sure you need to check only the first character for numeric? All
my credit cards have only numbers. To test for length 14 and only
numbers, you can change the above to

WHERE LEN(YourColumn) = 14
AND YourColumn NOT LIKE '%[^0-9]%'

And my two credit-cards have 16-digit numbers...
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Fri, 14 Sep 2007 21:38:51 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hu**@perFact.R EMOVETHIS.info. INVALID) writes:

>Are you sure you need to check only the first character for numeric? All
my credit cards have only numbers. To test for length 14 and only
numbers, you can change the above to

WHERE LEN(YourColumn) = 14
AND YourColumn NOT LIKE '%[^0-9]%'

And my two credit-cards have 16-digit numbers...

Heh! So has mine - I didn't even think of that while posting my reply.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[--CELKO--]

>I have a client that was allowing people to store credit card numbers in plain text, in an application. I need to rip through the table and replace every instance of credit card numbers with "x" and the last 4 digits. <<

You might want to learn the difference between a field and column
before you write anymore SQL -- like what a constraint is. I assume
that youmeant 16 digits, broken into groups of 4 digits.

UPDATE Foobar
SET creditcard_nbr
= 'xxxx-xxxx-xxxx-" + SUBSTRING( creditcard_nbr, 13, 16);

Having done this, put all of this in the DDL. Mop the floor,but fix
the leak!!