Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Search a string from multiple column in a table Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 12:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:12 PM
Points: 186, Visits: 1,017
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

Post #1389527
Posted Wednesday, November 28, 2012 1:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Kent user group
Post #1389566
Posted Wednesday, November 28, 2012 2:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1389583
Posted Thursday, November 29, 2012 4:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:12 PM
Points: 186, Visits: 1,017
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.
Post #1390425
Posted Thursday, November 29, 2012 8:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1390580
Posted Thursday, November 29, 2012 9:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:59 AM
Points: 191, Visits: 904
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
Post #1390678
Posted Thursday, November 29, 2012 10:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1390712
Posted Thursday, November 29, 2012 11:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
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 - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1390732
Posted Thursday, November 29, 2012 9:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:12 PM
Points: 186, Visits: 1,017
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
Post #1390995
Posted Thursday, November 29, 2012 9:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:12 PM
Points: 186, Visits: 1,017
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.
Post #1390996
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse