Search a string from multiple column in a table

  • Hi

    I want to search a string in multiple columns and i want the result from the column which has matching string.

    I solved the problem, is there any better solution than this

    DECLARE @City TABLE (CityID int,CityName varchar(100),AlternateCityName1 varchar(100),AlternateCityName2 varchar(100))

    INSERT INTO @City(CityID,CityName,AlternateCityName1,AlternateCityName2)

    SELECT 1,'Cochin','Kochi','Ernakulam'

    UNION ALL

    SELECT 2,'Kollam','Quillon',NULL

    UNION ALL

    SELECT 3,'Mumbai','Bombay',NULL

    UNION ALL

    SELECT 4,'Chennai','Madras',NULL

    UNION ALL

    SELECT 5,'Kolkata','Calcutta',NULL

    --SELECT * FROM @City

    DECLARE @SearchString varchar(20) = 'k'

    ;WITH City_CTE(CityID,Name) AS (

    SELECT

    CityID,

    'Name' = CASE

    WHEN CityName like (@SearchString + '%') THEN CityName

    WHEN AlternateCityName1 like (@SearchString + '%') THEN AlternateCityName1

    WHEN AlternateCityName2 like (@SearchString + '%') THEN AlternateCityName2

    END

    FROM @City )

    SELECT * FROM City_CTE WHERE Name IS NOT NULL

  • Although your solution may work perfectly well, you are not thinking about scale.

    What happens when you get more than 10 rows ?

    Compare your solution to a simple select as show below

    drop table city

    go

    Create TABLE city(CityID int identity Primary key,CityName varchar(100),AlternateCityName1 varchar(100),AlternateCityName2 varchar(100))

    go

    Create index idxName on City(CityName)

    go

    Create index idxAlternate on City(AlternateCityName1) where AlternateCityName1 is not null

    go

    Create index idxAlternate2 on City(AlternateCityName2) where AlternateCityName2 is not null

    go

    INSERT INTO City(CityName,AlternateCityName1,AlternateCityName2)

    select city,city,city from AdventureWorks2012.Person.Address

    go

    set statistics io on

    go

    Select * from City

    where CityName like 'Mad%'

    or AlternateCityName1 like 'Mad%'

    or AlternateCityName2 like 'Mad%'

    go

    DECLARE @SearchString varchar(20) = 'mad'

    ;WITH City_CTE(CityID,Name) AS (

    SELECT

    CityID,

    'Name' = CASE

    WHEN CityName like (@SearchString + '%') THEN CityName

    WHEN AlternateCityName1 like (@SearchString + '%') THEN AlternateCityName1

    WHEN AlternateCityName2 like (@SearchString + '%') THEN AlternateCityName2

    END

    FROM City )

    SELECT * FROM City_CTE WHERE Name IS NOT NULL



    Clear Sky SQL
    My Blog[/url]

  • may be a Freetext index is a better option

    Every rule in a world of bits and bytes, can be bend or eventually be broken
    MyBlog About Common dialog control
    A Visualizer for viewing SqlCommand object script [/url]

  • Thanks dave

    Is there any performance issue if i create Filterindex for each AlternateCityName column.

    I want to load thi city in a text box while typing the characters.If i i use ur SELECT statement it will display result from all the 3 clolumns which i don't want..i only want the matching characters from the respective column.

  • Try this:

    SELECT S.CityId, C.Name

    FROM @City AS S

    CROSS APPLY (VALUES (CityName),(AlternateCityName1),(AlternateCityName2)) C(Name)

    WHERE C.Name LIKE @SearchString + '%'

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Don't know if this is exactly what you are wanting, but I wrote this to search all tables for occurance of given string. Just change the database context line, and the string to search for. It creates a script. Just cut and paste to new window and run it.

    It will probably do a table scan anyway, so what does a cursor hurt?...........

    /* ============================================================================================================== */

    /* Create scripts to search each table's string columns for specified string value */

    /* ============================================================================================================== */

    /*Created Date: 08/29/2012

    By: vikingDBA

    Modifications:

    Dependencies:

    Summary:

    This script automates the creation of scripts to search each table, and its string columns, for a specific string value. Just

    set the variable @sfi to be the string to search for. If other data types need to be searched, just add them to the list

    of data types that is in the code.

    This creates scripts for all tables.

    */

    USE MyDatabase-- Set the database context

    GO

    SET NOCOUNT ON

    DECLARE @SN varchar(128)

    DECLARE @TN varchar(128)

    DECLARE @de varchar(128)

    DECLARE @cmd varchar(4000)

    DECLARE @dt varchar(128)

    DECLARE @sfi varchar(4000)

    DECLARE @tt varchar(128)

    /* ================================================================================================= */

    --User Settable Variables

    SET @sfi = 'Something To Look For'-- What string to search for

    /* ================================================================================================= */

    SELECT CONVERT(varchar(128),TABLE_SCHEMA) AS 'SchemaName',

    CONVERT(varchar(128),TABLE_NAME) AS 'TableName',

    CONVERT(varchar(128),COLUMN_NAME) AS 'DataElement',

    CONVERT(int,ORDINAL_POSITION) AS 'OrdinalPosition',

    CONVERT(varchar(128),DATA_TYPE) AS 'DataType',

    CONVERT(varchar(128),'') AS TableType

    INTO #dummycol

    FROM information_schema.columns gg

    ORDER BY SchemaName, TableName, OrdinalPosition

    UPDATE #dummycol SET TableType = 'VIEW' WHERE EXISTS (SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = #dummycol.SchemaName AND TABLE_NAME = #dummycol.TableName AND TABLE_TYPE = 'VIEW')

    SELECT * FROM #dummycol

    PRINT '-- If values exist in particular table or view, just highlight the select statement and run for desired table to get exact rows'

    DECLARE myCursorVariable CURSOR FOR

    SELECT DISTINCT SchemaName, TableName, TableType FROM #dummycol ORDER BY SchemaName, TableName

    OPEN myCursorVariable

    -- Loop through all the files for the database

    FETCH NEXT FROM myCursorVariable INTO @SN, @TN, @tt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmd = ''

    DECLARE myCursorVariable2 CURSOR FOR

    SELECT DataElement, DataType FROM #dummycol WHERE SchemaName = @SN AND TableName = @TN ORDER BY OrdinalPosition

    OPEN myCursorVariable2

    -- Loop through all the files for the database

    FETCH NEXT FROM myCursorVariable2 INTO @de, @dt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    if @dt IN ('char','varchar','text','ntext','nchar','nvarchar')

    BEGIN

    if @cmd <> '' SET @cmd = @cmd + CHAR(13)

    SET @cmd = @cmd + 'OR [' + @de + '] LIKE ' + '''' + '%' + @sfi + '%' + ''''

    END

    FETCH NEXT FROM myCursorVariable2 INTO @de, @dt

    END

    CLOSE myCursorVariable2

    DEALLOCATE myCursorVariable2

    if @cmd <> ''

    BEGIN

    if @tt = 'VIEW'

    PRINT CHAR(13) + CHAR(13) + '--View'

    else

    PRINT CHAR(13) + CHAR(13)

    PRINT 'if EXISTS ('

    PRINT 'SELECT * FROM ' + @SN + '.' + @TN + CHAR(13) + 'WHERE ' + SUBSTRING(@cmd,4,LEN(@cmd) -3)

    PRINT ')'

    PRINT char(9) + 'BEGIN'

    PRINT char(9) + char(9) + 'SELECT * FROM ' + @SN + '.' + @TN + CHAR(13) + 'WHERE ' + SUBSTRING(@cmd,4,LEN(@cmd) -3)

    PRINT char(9) + 'END'

    PRINT CHAR(9) + 'PRINT ''Records found in ' + @SN + '.' + @TN + ''''

    END

    FETCH NEXT FROM myCursorVariable INTO @SN, @TN, @tt

    END

    CLOSE myCursorVariable

    DEALLOCATE myCursorVariable

    DROP TABLE #dummycol

    SET NOCOUNT OFF

  • vikingDBA (11/29/2012)


    Don't know if this is exactly what you are wanting, but I wrote this to search all tables for occurance of given string. ...

    Don't you think it's a bit overkill to use it for searching string in three known columns of one known table...;-)

    Actually, there few ways are available on inet to do the same:

    http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • dilipd006 (11/29/2012)


    Thanks dave

    Is there any performance issue if i create Filterindex for each AlternateCityName column.

    I want to load thi city in a text box while typing the characters.If i i use ur SELECT statement it will display result from all the 3 clolumns which i don't want..i only want the matching characters from the respective column.

    Sounds like you're trying to do an autofill on a text box as a user starts typing. If that's the case you'd get the entire list of city names and load it to a dataset in memory and have the application do the autofill function instead of querying the database each time the user types a character...

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • Want a cool sig (11/29/2012)


    dilipd006 (11/29/2012)


    Thanks dave

    Is there any performance issue if i create Filterindex for each AlternateCityName column.

    I want to load thi city in a text box while typing the characters.If i i use ur SELECT statement it will display result from all the 3 clolumns which i don't want..i only want the matching characters from the respective column.

    Sounds like you're trying to do an autofill on a text box as a user starts typing. If that's the case you'd get the entire list of city names and load it to a dataset in memory and have the application do the autofill function instead of querying the database each time the user types a character...

    Thanks for the suggestion..can you provide some links it will be very helpfull

  • Eugene Elutin (11/29/2012)


    Try this:

    SELECT S.CityId, C.Name

    FROM @City AS S

    CROSS APPLY (VALUES (CityName),(AlternateCityName1),(AlternateCityName2)) C(Name)

    WHERE C.Name LIKE @SearchString + '%'

    Thanks Eugene..never known we can use Cross Apply in this way.

  • dilipd006 (11/29/2012)


    Eugene Elutin (11/29/2012)


    Try this:

    SELECT S.CityId, C.Name

    FROM @City AS S

    CROSS APPLY (VALUES (CityName),(AlternateCityName1),(AlternateCityName2)) C(Name)

    WHERE C.Name LIKE @SearchString + '%'

    Thanks Eugene..never known we can use Cross Apply in this way.

    But, once again, you need to think of scale. This will still use a scan to resolve it.

    Is there any performance issue if i create Filterindex for each AlternateCityName column.

    Naturally, any index carries a performance hit at maintenance time, generally the benifits more than outwieigh that especially with relatively static data such as City names.



    Clear Sky SQL
    My Blog[/url]

  • Dave Ballantyne (11/29/2012)


    dilipd006 (11/29/2012)


    Eugene Elutin (11/29/2012)


    Try this:

    SELECT S.CityId, C.Name

    FROM @City AS S

    CROSS APPLY (VALUES (CityName),(AlternateCityName1),(AlternateCityName2)) C(Name)

    WHERE C.Name LIKE @SearchString + '%'

    Thanks Eugene..never known we can use Cross Apply in this way.

    But, once again, you need to think of scale. This will still use a scan to resolve it.

    Is there any performance issue if i create Filterindex for each AlternateCityName column.

    Naturally, any index carries a performance hit at maintenance time, generally the benifits more than outwieigh that especially with relatively static data such as City names.

    No doubt, it will do table scan. However

    Select * from City

    where CityName like 'Mad%'

    or AlternateCityName1 like 'Mad%'

    or AlternateCityName2 like 'Mad%'

    does it too...

    To avoid it, based on indexes from your example, I think the best tactic here would be using UNION ALL:

    SELECT S.CityId, s.CityName

    FROM dbo.City AS S

    WHERE s.CityName LIKE 'mad%'

    UNION ALL

    SELECT S.CityId, s.AlternateCityName1

    FROM dbo.City AS S

    WHERE S.AlternateCityName1 LIKE 'mad%'

    UNION ALL

    SELECT S.CityId, s.AlternateCityName2

    FROM dbo.City AS S

    WHERE S.AlternateCityName2 LIKE 'mad%'

    This one will do three index seeks...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Three seeks here,

    Anyway the point is that the optimzer is now able to choose between a seek and scan not the developer 🙂



    Clear Sky SQL
    My Blog[/url]

  • Dave Ballantyne (11/30/2012)


    Three seeks here,

    Anyway the point is that the optimzer is now able to choose between a seek and scan not the developer 🙂

    Cannot see the image you've attached, but I believe you 🙂

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

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