Special Search - 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)

Special Search

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 09:28:32

Hi,
Is this possible without using full-text searching achieves this scenario?
Retrieve all rows that contains word of this phrase: 'a b'
Also the words of phrases is variable and much.

Example:

col1
---------------
a b c e x b w h <-- Selected
s w
w b w u x o a <--Selected
y d w

DECLARE @a VARCHAR(50) 
SET @a = N'a b';

SELECT col1
FROM
(
select 'a b c e x b w h' union
select 'y d w'  union
select 's w' union
select 'a w b w u x o a' )D(col1)
WHERE 
  col1 like '%' + LEFT(@a, CHARINDEX(' ', @a)-1) + '%'
AND  col1 LIKE '%' + REVERSE(LEFT(REVERSE(@a), CHARINDEX(' ', REVERSE(@a))-1)) + '%';
/*
col1
---------------
a b c e x b w h
a w b w u x o a
*/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-05 : 09:34:21

Substitite the spaces in your search wildcards

DECLARE	@a VARCHAR(50) 
SET	@a = N'a b'

SELECT	col1
FROM	(
		SELECT 'a b c e x b w h' UNION ALL
		SELECT 'y d w' UNION ALL
		SELECT 's w' UNION ALL
		SELECT 'w b w u x o a'
	) AS d(col1)
WHERE	col1 LIKE '%' + REPLACE(@a, ' ', '% %') + '%'
	OR col1 LIKE '%' + REPLACE(REVERSE(@a), ' ', '% %') + '%'

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 09:44:39

Good. But...
This approach will not be correct.

DECLARE	@a VARCHAR(50) 
SET	@a = N'a b c'

SELECT	col1
FROM	(
		SELECT 'a b c e x b w h' UNION ALL
		SELECT 'y d w' UNION ALL
		SELECT 's w' UNION ALL
		SELECT 'w b w u x o a c' <-- Must selected!
	) AS d(col1)
WHERE	col1 LIKE '%' + REPLACE(@a, ' ', '% %') + '%'
	OR col1 LIKE '%' + REPLACE(REVERSE(@a), ' ', '% %') + '%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-05 : 12:26:49

[code]DECLARE @a VARCHAR(50)
SET @a = N'a b c'

SELECT d.col1
FROM (
SELECT 'a b c e x b w h' UNION ALL
SELECT 'y d w' UNION ALL
SELECT 's w' UNION ALL
SELECT 'w b w u x o a'
) AS d(col1)
CROSS APPLY dbo.fnParseList(' ', @a) AS f
WHERE d.col1 LIKE '%' + f.Data + '%'
GROUP BY d.col1[/code]

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 13:32:12

Thanks Peso.
I changed your query a bit:

DECLARE	@a VARCHAR(50) 
SET	@a = N's w'

SELECT		d.col1
FROM		(
			SELECT 'a b c e x b w h' UNION ALL
			SELECT 'y d w' UNION ALL
			SELECT 's w' UNION ALL
			SELECT 'w b w u x o a'
		) AS d(col1)
CROSS APPLY	dbo.fnParseList(' ', @a) AS f
WHERE		d.col1 LIKE '%' + f.Data + '%'
GROUP BY	d.col1
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.fnParseList(' ', @a));

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-05 : 13:44:44

Where is this

dbo.fnParseList

Coming from?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-05 : 13:46:41

quote:
Originally posted by X002548

Where is this

dbo.fnParseList

Coming from?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

From here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources