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
-
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 + '%'
-
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
-
dilipd006 (11/29/2012)
Thanks daveIs 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 daveIs 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.
-
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 Citywhere 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...
-
Three seeks here,
Anyway the point is that the optimzer is now able to choose between a seek and scan not the developer 🙂
-
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 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply