none
Query to select street address RRS feed

  • Question

  • Hi!

    I'm looking for people living at "BAKER STREET 11 (sometimes followed by stuff)"
    Basically, I think the address I look for should not have a digit immediately afterwards but could continue with anything. 

    What would a valid query be?

    SELECT * FROM PEOPLE WHERE ADDRESS LIKE 'BAKER STREET 11[not a digit here]and then anything or nothing' 

    These should be found:
    BAKER STREET 11
    BAKER STREET 11A
    BAKER STREET 11 TOP FLOOR
    BAKER STREET 11 3RD FLOOR
    BAKER STREET 11, FLAT 413
    But not these:
    BAKER STREET 411
    BAKER STREET 3 FLAT 11
    BAKER STREET 114

    Kind regards,
    Ture Magnusson
    Karlstad, Sweden

    Friday, December 13, 2019 2:11 PM

Answers

All replies

  • SELECT * FROM PEOPLE WHERE ADDRESS LIKE 'BAKER STREET 11%';

    A Fan of SSIS, SSRS and SSAS

    Friday, December 13, 2019 2:14 PM
  • Thank you Guoxiong

    But that would find BAKER STREET 114 as well, which I don't want.

    I'm looking for "BAKER STREET 11[not a digit here]and pehaps more text here"

    Friday, December 13, 2019 2:18 PM
  • This works but I would like to avoid the "AND" if possible

    SELECT * FROM PEOPLE
    WHERE ADDRESS LIKE 'BAKER STREET 11%'
    AND ADDRESS NOT LIKE 'BAKER STREET 11[0123456789]%')

     
    Friday, December 13, 2019 2:23 PM
  • DECLARE @Test TABLE (sname VARCHAR(200))
    
    INSERT INTO @test (sname)
    VALUES ('BAKER STREET 114'),('BAKER STREET 11'),('BAKER STREET 11A'),('BAKER STREET 11A Top')
    
    
    SELECT * FROM @test WHERE sname LIKE 'BAKER STREET 11%[^0-9]' OR sname = 'BAKER STREET 11'


    Hope it Helps!!

    Friday, December 13, 2019 2:25 PM
  • Thanks Stan!

    It works and it's a bit nicer than mine but there's still an "OR" in your query.

    Is it possible to use just one single condition, without AND or OR ?

    Friday, December 13, 2019 2:37 PM
  • SELECT * FROM @test WHERE sname+'|' LIKE 'BAKER STREET 11[^0-9]%'
    • Marked as answer by TureSweden Friday, December 13, 2019 2:55 PM
    Friday, December 13, 2019 2:49 PM
    Moderator
  • SELECT * FROM PEOPLE WHERE ADDRESS LIKE 'BAKER STREET 11[^0-9]%';

    A Fan of SSIS, SSRS and SSAS

    Friday, December 13, 2019 2:51 PM
  • Thank you Jingyang!

    That seems to work perfectly. :)

    Friday, December 13, 2019 2:59 PM