#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    Question 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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    select *, 'record has no number in address'
    from yourtable
    where patindex('%[0-9]%',address) = 0

    see PATINDEX

    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    2
    Rep Power
    0

    Talking


    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

IMN logo majestic logo threadwatch logo seochat tools logo