T-SQL Using LIKE with IN to match values


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: T-SQL Using LIKE with IN to match values

  1. #1
    Join Date
    Mar 2009
    Location
    Durham, England
    Posts
    4

    T-SQL Using LIKE with IN to match values

    Hi all,

    I've spent a good while searching now and haven't found any answers for this so I suspect it isn't possible.

    I have been given a list of street names and I need to select all the records from our address table that match those street names.

    Ordinarily I'd use something like
    SELECT * FROM Address WHERE Street IN ('Main Road', 'Last Street', 'First Avenue')
    etc.

    However, the list I'm given is just the street name, it doesn't include the house number, but the Street column in the Address table may or may not include the house number.

    eg. Street column values could include '3 Main Road', 'Main Road', '4A Main Road', '2B Last Street', '1 Last Street', 'First Avenue'.

    I wanted to do something like
    SELECT * FROM Address WHERE Street LIKE IN ('%Main Road%', '%Last Street%', '%First Avenue%')
    so that it would match any characters in Street before and after the street name.

    Any ideas how I can go about this?

    Thanks

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Welcome to DevX

    Something like this should work for you
    Code:
    SELECT * FROM Address 
    WHERE street LIKE '%box%'
    OR street LIKE '%avenue%'
    OR street LIKE '%street%'
    etc
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Mar 2009
    Location
    Durham, England
    Posts
    4
    Welcome to DevX

    Something like this should work for you
    Code:
    SELECT * FROM Address
    WHERE street LIKE '%box%'
    OR street LIKE '%avenue%'
    OR street LIKE '%street%'
    etc
    Thanks Hack. I should have been a bit clearer though, I can't use OR statements as the list of street names is passed to me as a string or varchar values which I then split out into a table of values so I can perform an IN on them. This is actually part of a much much larger SELECT which is driven by a user search function containing many different filters.

    I've come up with something which is sort of working at the moment but is very long and complicated and involves using SUBSTRING, ISNUMERIC and CHARINDEX to strip out any leading house numbers!

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Given what it being passed to you then you probably will have little choice but to go with what you have already come up with regardless of its length or complications.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Mar 2009
    Location
    Durham, England
    Posts
    4
    Thanks, that's what I suspected but thought I'd throw it out there in case anyone had any bright ideas.

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Well, before we toss in the proverbial towel, lets try one more thing.

    Take a typical string that is being passed to you from which you must extract your address information. Sanitize it to protect the innocent, and post it here.

    Perhaps after playing around with it a bit one of us can come up with something.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Mar 2009
    Location
    Durham, England
    Posts
    4
    Here's the contents of the Street column of the Address table (identities have been changed to protect the innocent):

    30 Greek Street
    88 Oldglass Street
    Claypark Road
    The Potteries
    Cotton Terrace
    5 West Hole Road
    1 Cardinal Way
    Middleton Road
    Chapel Street
    19B Oldglass Street
    The Circle
    131 Fiveways Drive
    22 Berryturn
    24 Fluid Green
    2 Cardinal Way
    Fordstream Road
    Grafton Park
    Cranberry Road
    Social Road
    Rabbit Hill
    Fiveways Drive
    151 MERTON ROAD
    3 Princes Avenue
    23 Princes Avenue
    2 BOTTOMFIELD GARDENS

    This is the stored procedure I've written (cut down version):
    CREATE PROCEDURE dbo.sel_Search
    @LowerDOB datetime,
    @HigherDOB datetime,
    @Streets nvarchar(4000)
    AS
    BEGIN
    SELECT DISTINCT
    p.PersonID,
    p.Forename,
    p.Surname,
    ad.Street,
    ad.Town,
    FROM
    dbo.Person p
    JOIN
    dbo.Address ad
    ON ad.AddressID = p.AddressID
    WHERE
    convert(datetime, p.DateOfBirth, 103) BETWEEN @LowerDOB AND @HigherDOB
    AND
    substring(fad.Street, case when ISNUMERIC(substring(fad.Street, 1, 1)) = 1 then charindex(' ', fad.Street) + 1 else 1 end, len(fad.Street) - case when ISNUMERIC(substring(fad.Street, 1, 1)) = 1 then charindex(' ', fad.Street) - 1 else 0 end) IN (SELECT * FROM dbo.ConvertListToTable(@Streets))

    ConvertListToTable just takes in a comma separated list and splits it into a table of values.

    And here's a typical value for that would be passed in as @Streets:
    'FIVEWAYS DRIVE,OLDGLASS STREET,MERTON ROAD'

  8. #8
    Join Date
    Mar 2009
    Location
    Jakarta, Indonesia
    Posts
    1
    Hi, Marsha
    If you have already function ConvertListToTable that returns table with StreetName column, then you can INNER JOIN this function with Address but with criteria ON ConvertListToTable(@Street) LIKE '%'+ad.Street+'%'.
    In this case you can avoid use of function SUBSTRING, ISNUMERIC etc. for the filter in your stored procedure

  9. #9
    Join Date
    Oct 2006
    Posts
    7

    Smile There is a function that Word Uses Called SOUNDEX

    You may want to look at the SOUNDEX function. This is what MS-Office uses to do the spelling check.

    SELECT * FROM Address WHERE SOUNDEX(Street) IN (SOUNDEX('Main Road'), SOUNDEX('Last Street'), SOUNDEX('First Avenue'))

  10. #10
    Join Date
    May 2009
    Posts
    1
    I think another avenue may be to create a temporary table in your procedure and modify your ConvertListToTable to populate the temp table and then use a subquery to select the temp table using the LIKE syntax. I have used similar coding with good results. Good luck!

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 04:14 PM
  2. Help with looping a dynamic sql string
    By jeff in forum ASP.NET
    Replies: 1
    Last Post: 06-07-2002, 11:12 AM
  3. Reuse unused IDENTITY values in SQL Server
    By Sarita Pinto in forum Database
    Replies: 4
    Last Post: 03-17-2002, 05:46 AM
  4. Replies: 4
    Last Post: 02-21-2002, 12:43 PM
  5. Replies: 1
    Last Post: 02-20-2002, 11:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center