Puzzling SELECT results - 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)

Puzzling SELECT results

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-26 : 06:34:30

I have this

SELECT id, name, locationid, categoryid, keywords, imagetype, priority
FROM dbo.fin_images
WHERE (categoryid LIKE '%1:%') OR
(categoryid LIKE '%2:%') OR
(categoryid LIKE '%3:%') OR
(categoryid LIKE '%4:%') OR
(categoryid LIKE '%5:%') OR
(categoryid LIKE '%43:%') OR
(categoryid LIKE '%68:%') OR
(categoryid LIKE '%30:%') OR
(categoryid LIKE '%46:%') OR
(categoryid LIKE '%69:%') OR
(categoryid LIKE '%71:%') OR
(categoryid LIKE '%72:%') OR
(categoryid LIKE '%73:%') OR
(categoryid LIKE '%74:%') OR
(categoryid LIKE '%56:%') OR
(categoryid LIKE '%57:%') OR
(categoryid LIKE '%59:%') OR
(categoryid LIKE '%62:%') OR
(categoryid LIKE '%61:%') OR
(categoryid LIKE '%63:%') OR
(categoryid LIKE '%64:%') OR
(categoryid LIKE '%65:%') OR
(categoryid LIKE '%40:%') OR
(categoryid LIKE '%41:%') OR
(categoryid LIKE '%44:%') OR
(categoryid LIKE '%66:%') OR
(categoryid LIKE '%67:%')

It returns results when categoryid is

:6:75:80:
:7:76:84:

Cant seem to see where I went wrong

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 06:44:35

Nothing wrong

:6:75:80: is satisfied by categoryid LIKE '%5:%' and
:7:76:84: is satisfied by categoryid LIKE '%4:%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 07:01:35

For '%44:%', you also get 144, 244, 234243244 and so on...

SELECT	id,
	name,
	locationid,
	categoryid,
	keywords,
	imagetype,
	priority
FROM	dbo.fin_images
WHERE	':' + categoryid + ':' LIKE '%:1:%'
	OR ':' + categoryid + ':' LIKE '%:2:%'
	OR ':' + categoryid + ':' LIKE '%:3:%'
	OR ':' + categoryid + ':' LIKE '%:4:%'
	OR ':' + categoryid + ':' LIKE '%:5:%'
	OR ':' + categoryid + ':' LIKE '%:43:%'
	OR ':' + categoryid + ':' LIKE '%:68:%'
	OR ':' + categoryid + ':' LIKE '%:30:%'
	OR ':' + categoryid + ':' LIKE '%:46:%'
	OR ':' + categoryid + ':' LIKE '%:69:%'
	OR ':' + categoryid + ':' LIKE '%:71:%'
	OR ':' + categoryid + ':' LIKE '%:72:%'
	OR ':' + categoryid + ':' LIKE '%:73:%'
	OR ':' + categoryid + ':' LIKE '%:74:%'
	OR ':' + categoryid + ':' LIKE '%:56:%'
	OR ':' + categoryid + ':' LIKE '%:57:%'
	OR ':' + categoryid + ':' LIKE '%:59:%'
	OR ':' + categoryid + ':' LIKE '%:62:%'
	OR ':' + categoryid + ':' LIKE '%:61:%'
	OR ':' + categoryid + ':' LIKE '%:63:%'
	OR ':' + categoryid + ':' LIKE '%:64:%'
	OR ':' + categoryid + ':' LIKE '%:65:%'
	OR ':' + categoryid + ':' LIKE '%:40:%'
	OR ':' + categoryid + ':' LIKE '%:41:%'
	OR ':' + categoryid + ':' LIKE '%:44:%'
	OR ':' + categoryid + ':' LIKE '%:66:%'
	OR ':' + categoryid + ':' LIKE '%:67:%'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources