Search a String for Numeric Values

I am being sent very large enrollment files monthly from a clearing house. That clearing house is using postage software to validate addresses, supposedly. However, the return mail we received indicated they had neglected to filter out addresses like "No Address" or "Member Termed". Consequently, we have decided to interrogate the data here before authorizing the fulfillment and paying for the postage of the next mailing. To date we have over 2 million records to examine. I use MH SQL2000 as my data server.

I was thinking a simple solution would be find records that had no Numeric values in [address1]. To my surprise, MS SQL2000 doesn't appear to have a function which I can see that would assist in my quest.


Advice?

Noel Meeks

select *, 'record has no number in address'
from yourtable
where patindex('%[0-9]%',address) = 0

see PATINDEX

rudy
http://r937.com/

I had written this:

SELECT GroupNumber, FirstName, LastName, Initial, Address1,
City, State, ZipCode, SSN, 2 AS Code
FROM tblDataModifier
WHERE Address1 NOT LIKE '%1%' and Address1 NOT LIKE '%2%' and Address1 NOT LIKE '%3%' and Address1 NOT LIKE '%4%' and Address1 NOT LIKE '%5%' and Address1 NOT LIKE '%6%' and Address1 NOT LIKE '%7%' and Address1 NOT LIKE '%8%' and Address1 NOT LIKE '%9%' and Address1 NOT LIKE '%0%'


And it worked but it appears what you have suggested is the simple and more efficient way to go.


Thank you so very much.

I let you know how it works.

Noe