Text search with data from a table

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

General SQL Server Forums

New to SQL Server Programming

Text search with data from a table

Author

Topic

jbednarski
Starting Member

1 Post

Posted - 2011-10-23 : 21:27:20

Hello and thanks for reading my post:

I have an existing T-SQL query that returns a primary key, with a second column of free text separated by commas as follows: "Head, shoulders, knees, and toes"

There will be multiple rows returned, each with different text.

I have a comparison table. This table is built with a primary key as the first column, and a second column seeded with target text as follows:

1 Banana
2 Cherry
3 7-UP
4 Knees
5 Shoulders

I wish to compare the text against the table, so that if any of the keywords in the table are found, a Y is returned. So, in my example, since two keywords are found in the text, I wish a Y to be returned, else N or NULL.

Any suggestions would be most appreciated!

Washu
Starting Member

2 Posts

Posted - 2011-10-23 : 23:05:41

You can use the sql CASE statement to output a column of 'Y' or 'N'.

SELECT
CASE <Col Name>
WHEN 'Knees' THEN 'Y'
WHEN 'Toes' THEN 'Y'
ELSE 'N'
END

Here is an article that helped me when I encountered a similar situation.
http://www.4guysfromrolla.com/webtech/102704-1.shtml

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 04:50:21

[code]
SELECT ID,Text,CASE WHEN MIN(LEN(ReplacedText))<LEN(Text) THEN 'Y' ELSE 'N' END
FROM
(
SELECT t1.ID,t1.Text,REPLACE(t1.Text,t2.TargetText,'') AS ReplacedText
FROM Table1 t1
CROSS JOIN table2
)t
GROUP BY ID,Text
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources