June 15, 2016 at 10:20 pm
I have a table student with addresses:
studentname, address1,address2
'john','8, Park View','newington'
'Michael','10 Way street','stoke'
'Steve','55, Johnston Road','London'
Please note some address1 have a comma and others have just one space separating the number part and the first word part.
I want a storedprocedure to select the student with their address based on inputting or passing address1
e.g: 8, Park View
or 8 Park View
Thank you for your speedy response in advance.
June 18, 2016 at 1:36 pm
I think using the T-SQL LIKE clause would help in this scenario.
e.g. in WHERE clause of stored proc use ,
WHERE Address = @inputaddress or Address LIKE LEFT(@inputaddress,1)+'%'
June 18, 2016 at 11:00 pm
I think what you are looking for is to set up a full text catalog and index on the address columns. There are many articles that show how this can be done, I believe there are articles on this site that will assist here.
...
June 18, 2016 at 11:40 pm
Thank you for reply. But
if the address 1 column of the table has [8 jumbo square] but user wants [8 Park View] then Left(@address1,1) will pick both [8 Jumbo Square] and [8 Park View] but user must get only [8 Park View] or [8, Park View]. What we know is that the database may have [8 Park View] or [8, Park View] and user can input [8, Park View] and this must find either [8, Park View] or [8 Park View] as the result.
June 18, 2016 at 11:42 pm
not sure if sql 2000, a legacy database can do this. I will have a look.
June 19, 2016 at 8:30 am
Use IN with a list of possible values:
IN("8 Park Lane","8,Park Lane","8, Park Lane").
Construct these values from your input.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply