Posted by Hugo Kornelis on 09/14/07 18:54

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

• Next in forum: Re: Reducing many-to-many to one-to-one
• Prev in forum: Re: Select Records and Insert into Text File?
• Thread view: Re: Search for specific length string in column

[Reply to this message]