Address search - TSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Address search - TSQL

Author

Topic

marco gwiliani
Starting Member

25 Posts

Posted - 2008-03-19 : 04:58:15

I have an address search on my website which is free text, so you could type in either a postcode or an address, examples would be:

"L1 3GB"
"Oxford Road"
"Oxford Road London"
"Oxford Road, London" - note the comma

The string from the search is passed to a sproc in ms sql 2005. My sproc at the moment works if a user types in just a postcode, e.g. "L1 3GB" or just a street name, e.g. "Oxford Road", but if a users types in "Oxford Road London" or "Oxford Road, London" (note the comma) my search will return NO data.

Can anyone advise how I can return results when the input string contains a comma, or indeed contains both an address and a city name which essentially in the database covers more than one colunm

Thanks Marco

SELECT Upper(Postcode) as 'Postcode', Upper(Address1) as 'Address1', Upper(Address2) as 'Address2', Upper(Address3) as 'Address3', Upper(Address4) as 'Address4', Upper(Address5) as 'Address5'
FROM Postcode
WHERE Address1 Like '%' + @address + '%'
OR Address2 Like '%' + @address + '%'
OR Postcode Like '%' + @address + '%'

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-19 : 05:44:36

But are you sure the data user types & the data in your table will contain only commas?
You can get rid of the commas using something like this.

Declare @str varchar(50)
set @str='Oxford Road, London'
select replace(@str,',','')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 05:49:44

may be Address1 Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%') + '%'
OR Address2 Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%')+ '%'
OR Postcode Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%')+ '%'

marco gwiliani
Starting Member

25 Posts

Posted - 2008-03-19 : 06:14:34

I believe that a comma is the most likely special character that will appear in the search string, so from your post it looks like a REPLACE will get rid of the comma, thanks for that.

Onto the next point, if the search criteria is "Oxford Road, London" and I remove the comma, I still get no results returned. My sproc would be as below, and in the table, Address1 = Oxford Road, Address2 = London - ie they are in separate columns in the table. Do I somehow need to break down the input string?

WHERE Address1 Like '%' + 'Oxford Road London' + '%'
OR Address2 Like '%' + 'Oxford Road London' + '%'
OR Postcode Like '%' + 'Oxford Road London' + '%'

thanks
MArco

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 06:41:15

Try combining them:-

RTRIM(LTRIM(ISNULL(Address1,'') + ISNULL(Address2,''))) Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%') + '%'
OR Postcode Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%')+ '%'

marco gwiliani
Starting Member

25 Posts

Posted - 2008-03-19 : 07:52:38

Hello, Do you think creating a view on the table with all the columns for each row already combined (If this is possible) would be worthwhile for this? Should make the query quicker rather than doing the combine at runtime. What do you think?
Cheers MArco

marco gwiliani
Starting Member

25 Posts

Posted - 2008-03-20 : 05:18:01

I managed to create a view, many thanks for your help. I now need to create an index and an unsure as have never created an index before. I will create a separate post for that. Cheers. MArco.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources