Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Query Column with letters

    I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?

    Is there a better alternative to:
    WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...

  2. #2
    Join Date
    Sep 2004
    Posts
    22
    if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by mitchell007
    if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?
    Well the data is already in there and we would rather have it disabled with the option of cleaning it up later rather than simply deleting such data.

  4. #4
    Join Date
    Sep 2004
    Posts
    22
    ok. Use the ISNUMERIC function... it'll return a 1 if the field is a valid number... 0 if it's not. That should be the easiest way to determine if there are letters in the postal code.

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    Good idea, but I must allow for hyphens.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya mean like

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT OFF
    CREATE TABLE myTable99(Col1 varchar(8000))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT '12345' UNION ALL
    SELECT 'Brett' UNION ALL
    SELECT 'North Dallas 40' UNION ALL
    SELECT '40-20' UNION ALL
    SELECT '123.45' UNION ALL
    SELECT '123456789012345678901234567890'
    GO
    
    SELECT * FROM myTable99
     WHERE ISNUMERIC(REPLACE(Col1,'-','')) = 1
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •