Post reply

Search String in a table column

  • 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"

  • October 11, 2009 at 8:05 am

    #1064524

    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

  • October 11, 2009 at 9:48 am

    #1064537

    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"

  • October 11, 2009 at 1:12 pm

    #1064554

    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 = '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 VARCHAR(50)

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

    SET @S = '%' + (SELECT SFor FROM #SString WHERE id = @id)

    SET @SQL = @SQL + @S + '%'')'

    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]

  • October 11, 2009 at 9:41 pm

    #1064575

    How about a Full Text Index?

  • October 13, 2009 at 3:08 am

    #1065091

    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...

  • October 13, 2009 at 9:39 am

    #1065268

    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]

  • October 15, 2009 at 1:39 pm

    #1066493

    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.

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

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