Post reply

Check if number exists in a column

  • September 20, 2019 at 10:28 pm

    Go to Answer

    #3682116

    Hello,

    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.

    Thanks,

    Tim

     

  • September 21, 2019 at 11:10 pm

    #3682313

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

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

  • September 22, 2019 at 6:37 pm

    Answer

    #3682341 

    ;WITH x AS
    (
    SELECT * 
      FROM (VALUES ('Flat 1','25 My Street',''),
                   ('33 My Street','',''),
                   ('','','33 My Street')) T(A1,A2,A3)
    )
    SELECT *,
           CASE        
                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 2 (of 2 total)

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