Check if number exists in a column

  • tim.stutzman

    SSC Enthusiast

    Points: 140


    I am pulling some address data from a legacy system and it has three address fields in the table (Address1, Address2 and Address3).  Over time users have been inconsistent to which address field they put the street address into.  One of them will have the street address where the other two will have generic customer related data.  Is there a way in SQL Server 2008 I can check which address field has the number in it and only query that column as the address.  My assumption will be that the address field with the number in it is contains the street address.

    I was thinking a CASE statement would work to get the output I desire (one field), but not sure how to test all three columns to check if there is a number in it.

    Any help would be appreciated.




  • Site Owners

    SSC Guru

    Points: 80379

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jonathan AC Roberts


    Points: 17172

    ;WITH x AS
    SELECT *
    FROM (VALUES ('Flat 1','25 My Street',''),
    ('33 My Street','',''),
    ('','','33 My Street')) T(A1,A2,A3)
    SELECT *,
    WHEN A3 LIKE '%[0-9]%' THEN A3
    WHEN A2 LIKE '%[0-9]%' THEN A2
    WHEN A1 LIKE '%[0-9]%' THEN A1
    ELSE ''
    END AddressLine1
    FROM x

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply