SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search a string from multiple column in a table


Search a string from multiple column in a table

Author
Message
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1330
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


Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7448 Visits: 8370
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
thava
thava
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 557
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
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1330
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.
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13096 Visits: 5478
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
vikingDBA
vikingDBA
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1298 Visits: 929
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
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13096 Visits: 5478
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
Want a cool Sig
Want a cool Sig
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1012 Visits: 705
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 Cool
I want to win the lotto :-D
I want a gf like Tiffa w00t Oh wait I'm married!:-D
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1330
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
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1330
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search