Search for specific length string in column

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

Re: Search for specific length string in column

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




wrote in message
news:1189779565.603908.70530@19g2000hsx.googlegroups.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
>

Re: Search for specific length string in column

On Fri, 14 Sep 2007 07:19:25 -0700, mike@mcarlson.net 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

Re: Search for specific length string in column

Hugo Kornelis (hugo@perFact.REMOVETHIS.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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Search for specific length string in column

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

>Hugo Kornelis (hugo@perFact.REMOVETHIS.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

Re: Search for specific length string in column

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