LIKE clause not liking spaces - 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 2000 Forums

Transact-SQL (2000)

LIKE clause not liking spaces

Author

Topic

BigJohnson
Starting Member

9 Posts

Posted - 2006-08-01 : 08:24:54

Hi all
I have a SP which contains a LIKE clause to retrieve a list of addresses. However, it seems that it doesn't like spaces within the search string.

eg
Highway - works ok
Highway Road - fails
36 Highway - fails

....WHERE SiteAdd LIKE '%'+@SiteAdd+'%'

Does any one know how to covercome this?
Many thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-01 : 08:31:01

A like statement will cope with spaces.
Could be that you have multiple spaces or it isn't really a space
You can test by
select 1 where 'Highway Road' like '%way %'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-01 : 08:33:24

[code]Create table #t (w varchar(2000))

Insert into #t values('Highway')
Insert into #t values('Highway Road')
Insert into #t values('36 Highway')
Insert into #t values('36 Lowway')
Insert into #t values('High way')
Insert into #t values(' Highway')

Select w from #t where w like 'Highway%'
-- Returns those start with Highway and rest may be anything

Select w from #t where w like '%Highway'
-- Returns those start with anything (including nothing) and the ending with Highway

Select w from #t where w like '%Highway%'
-- Returns those having Highway any place in the string[/code]

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 08:33:36

try this example

-- prepare test data
declare @test table (siteadd varchar(100))

insert	@test
select	'Highway' union all
select	'Highway Road' union all
select	'36 Highway'

declare @search table (what varchar(50))

insert	@search
select	' ' union all
select	'road' union all
select	'way' union all
select	'y r' union all
select	' h' union all
select	'6 ' union all
select	'high'

-- do the work
select		t.siteadd [This i have],
		'*' + s.what + '*' [This i search for], -- the stars is just there for clarification for the space search
		case when SiteAdd LIKE '%' + what + '%' then 'works ok' else 'fails' end Result
from		@test t
cross join	@search s
order by	1,
		2

Output is


36 Highway	* *	works ok
36 Highway	* h*	works ok
36 Highway	*6 *	works ok
36 Highway	*high*	works ok
36 Highway	*road*	fails
36 Highway	*way*	works ok
36 Highway	*y r*	fails
Highway		* *	fails
Highway		* h*	fails
Highway		*6 *	fails
Highway		*high*	works ok
Highway		*road*	fails
Highway		*way*	works ok
Highway		*y r*	fails
Highway Road	* *	works ok
Highway Road	* h*	fails
Highway Road	*6 *	fails
Highway Road	*high*	works ok
Highway Road	*road*	works ok
Highway Road	*way*	works ok
Highway Road	*y r*	works ok

Peter Larsson
Helsingborg, Sweden

BigJohnson
Starting Member

9 Posts

Posted - 2006-08-01 : 11:49:01

your all correct - it was a problem with the page code.
Many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources