Search String in a table column

Question

Post reply

Search String in a table column

Minaz Amin

SSChampion

Points: 11052
More actions
October 11, 2009 at 6:05 am

#216249

Hi,
I need a TSQL to search a string in a column. SUBString works but I should know the strarting postion & lenth. But on fly I will not be able to find.?
My search strings are present in another temporary tables column SearchSTR.
I need pass this SearchSTR and then my query should search Suscription table and should give me Supcription ID?
For example
I have column Subs in a table Suscription :
"Knowledge Management (KM):Articles"
"Knowledge Management (KM):Best Practices / White Papers"
"Knowledge Management (KM):General"
"Knowledge Management (KM):KM Resources / Links"
"SNA: The comba Marketing:Fusion Point: Brand Plans"
"LNA: The comba Marketing:Fusion Point: Brand Strategy"
I need to search any a string from these variables.
Search string can be "SNA: The comba Marketing" or "Fusion Point" or "Brand Strategy"
Thanks !!
"More Green More Oxygen !! Plant a tree today"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

Hi,

Wouldn't LIKE work for you?

select SubstriptionID from Subscription where Subs LIKE '%SNA: The comba Marketing%'

Regards

Piotr

...and your only reply is slàinte mhath

Minaz Amin SSChampion Points: 11052 More actions · Answer 2

Piotr.Rodak (10/11/2009)
Hi,
Wouldn't LIKE work for you?
select SubstriptionID from Subscription where Subs LIKE '%SNA: The comba Marketing%'
Piotr

This statement will return all the rows with the SNA value..

I need to pass a search string and this might match any of the pattern in the data present in the column and should extract that for me.

Regards

"More Green More Oxygen !! Plant a tree today"

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

You will have to create a dynamic T-SQL statement and then execute it.

In this case you pass in the id of the string you are searching for, that could be modified to pass in the string being searched for itself for exampe @s-2 = 'General"

:CREATE TABLE #SString (Id INT IDENTITY,SFOR VARCHAR(50))

INSERT INTO #SString

SELECT 'SNA: The comba Marketing' UNION ALL

SELECT 'Fusion Point' UNION ALL

SELECT 'Brand Strategy'

CREATE TABLE #BeingSearched(Id INT IDENTITY,Something VARCHAR(400))

INSERT INTO #BeingSearched

SELECT 'Knowledge Management (KM):Articles' UNION ALL

SELECT 'Knowledge Management (KM):Best Practices / White Papers' UNION ALL

SELECT 'Knowledge Management (KM):General' UNION ALL

SELECT 'Knowledge Management (KM):KM Resources / Links' UNION ALL

SELECT 'SNA: The comba Marketing:Fusion Point: Brand Plans' UNION ALL

SELECT 'LNA: The comba Marketing:Fusion Point: Brand Strategy'

DECLARE @id INT

SET @id = 1 --passed value here I just picked the value of 1

DECLARE @sql VARCHAR(500)

DECLARE @s-2 VARCHAR(50)

SET @sql ='SELECT Id,Something FROM #BeingSearched WHERE Something LIKE ('''

SET @s-2 = '%' + (SELECT SFor FROM #SString WHERE id = @id)

SET @sql = @sql + @s-2 + '%'')'

PRINT @sql -- remove after testing and verifying dynamic T-SQL

EXEC (@SQL)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

OTF SSCertifiable Points: 6104 More actions · Answer 4

OTF

SSCertifiable

Points: 6104

October 11, 2009 at 9:41 pm

#1064575

How about a Full Text Index?

irobertson Hall of Fame Points: 3695 More actions · Answer 5

How about a cross join?

Using Bitbucket's test tables above:

select bs.Id, bs.Something, ss.SFOR

from #BeingSearched bs

cross join #SString ss

where charindex(ss.SFOR, bs.Something) <> 0

Note this returns multiple hits per searched row due to multiple terms matching the input. Not sure if this would be the case with your data, but if so you could then pivot the results giving a column for each search term hit...

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

irobertson - hope you do not mind a little plagiarsm

Like your idea of a cross join, and in my example both the table being searched and the table containing the string to be searched for had to exist, further if the user want to pass in more than one string to be searched for creating a dynamic T-SQL statement with an "OR" clause can get to be tedious. So if you will excuse me I took your cross join and simplified and made my suggestion more flexible.

CREATE TABLE #BeingSearched(Id INT IDENTITY,Something VARCHAR(400))

INSERT INTO #BeingSearched

SELECT 'Knowledge Management (KM):Articles' UNION ALL

SELECT 'Knowledge Management (KM):Best Practices / White Papers' UNION ALL

SELECT 'Knowledge Management (KM):General' UNION ALL

SELECT 'Knowledge Management (KM):KM Resources / Links' UNION ALL

SELECT 'SNA: The comba Marketing:Fusion Point: Brand Plans' UNION ALL

SELECT 'LNA: The comba Marketing:Fusion Point: Brand Strategy' UNION ALL

SELECT 'Not here'

CREATE PROC Dbo.Searching

/* code for cross join by irobertson */

@Item1 VARCHAR(50),

@Item2 VARCHAR(50)

AS

CREATE TABLE #SString(SFOR VARCHAR(50))

IF LEN(@Item1) > 0

INSERT INTO #SString(SFOR) VALUES(@Item1)

IF LEN(@Item2) > 0

INSERT INTO #SString(SFOR) VALUES(@Item2)

select bs.Id, bs.Something, ss.SFOR

from #BeingSearched bs

cross join #SString ss

where charindex(ss.SFOR, bs.Something) <> 0

DROP TABLE #SString

DROP TABLE #BeingSearched

/*Run as Dbo.Searching 'link','Brand' OR Dbo.Searching 'link', ''*/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

hbrest Old Hand Points: 373 More actions · Answer 7

Chim Kalunta,

Full text index is nice.

Then you can use the 'CONTAIN' option in the WHERE clause.

See http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm

or MS Sqlserver 2005 Books online articles.