Query couple of columns? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query couple of columns?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-26 : 15:00:40

Hi,

I have a table, lets say there are 5 columns. 3 of them are varchar,one is datetime and the last one is int.

If user enters 'John', I would like to get all containing 'John'.
If user enters 1000 then all data containing 1000 should come.

Is there a way to do this? One input to search for almost every columns.

Best Regards.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 15:24:15

Show us some sample data of what you mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-27 : 02:10:25

Here is a sample data:

VendorNo SeriNo VendorName OdemeTutari SporTotoBorc OdemeTarih
111244 ASN250 KEMAL CEM 3201.00 1000.00 2011-06-02 00:00:00.000
103228 ASB300 CAN KEMAL 4969.92 50.00 2010-12-23 00:00:00.000
102072 ASN555 CEM GÜNGEN 1190.72 3450.32 2011-08-25 00:00:00.000
110285 ASC455 KEMAL TETIK 3282.35 1000.00 2011-03-03 00:00:00.000

Manigandan
Starting Member

4 Posts

Posted - 2012-06-27 : 02:26:22

Show the Sample Output

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-27 : 02:34:27

My Bad Sample Data is as follows:

VendorNo SeriNo VendorName OdemeTutari OdemeTarih
111300 ASD500 KEMAL CAN 1000 2011-06-02 00:00:00.000
100028 ADF300 CAN AKTÜRK 500 2010-12-23 00:00:00.000
300072 ASS1000 CEM KEMAL 300 2011-08-25 00:00:00.000
110500 ASF300 KEMAL TETIK 100 2011-03-03 00:00:00.000

And if I want to search for 300,I want to get all the rows.If I want to search for 'KEMAL', I want to get all rows except second row.

Manigandan
Starting Member

4 Posts

Posted - 2012-06-27 : 03:37:53

-- Refer this site you need means
--http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS

DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)

BEGIN TRY
SET @sqlCommand = 'SELECT * FROM ' + @schema + '.' + @table + ' WHERE'
SET @where = ''

SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''' + @schema + '''
AND TABLE_NAME = ''' + @table + '''
AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

EXEC (@cursor)

OPEN col_cursor
FETCH NEXT FROM col_cursor INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @where <> ''
SET @where = @where + ' OR'

SET @where = @where + ' ' + @columnName + ' LIKE ''' + @stringToFind + ''''
FETCH NEXT FROM col_cursor INTO @columnName
END

CLOSE col_cursor
DEALLOCATE col_cursor

SET @sqlCommand = @sqlCommand + @where
--PRINT @sqlCommand
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
PRINT 'There was an error'
IF CURSOR_STATUS('variable', 'col_cursor') <> -3
BEGIN
CLOSE col_cursor
DEALLOCATE col_cursor
END
END CATCH
GO

--CREATE TABLE TABLE1 (VendorNo BIGINT, SeriNo VARCHAR(100), VendorName VARCHAR(100), OdemeTutari BIGINT, OdemeTarih DATETIME)
--INSERT INTO TABLE1
--SELECT 111300,'ASD500','KEMAL CAN',1000,'2011-06-02 00:00:00.000' UNION
--SELECT 100028,'ADF300','CAN AKTÜRK', 500, '2010-12-23 00:00:00.000' UNION
--SELECT 300072,'ASS1000', 'CEM KEMAL', 300, '2011-08-25 00:00:00.000' UNION
--SELECT 110500,'ASF300', 'KEMAL TETIK', 100, '2011-03-03 00:00:00.000'

SELECT * FROM TABLE1

EXEC sp_FindStringInTable '%300%', 'dbo', 'TABLE1'
GO
EXEC sp_FindStringInTable '%kemal%', 'dbo', 'TABLE1'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-27 : 07:40:22

You might find this blog useful: http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx

The code she posted covers only character data types. You would need to make modifications if you want it to include numeric columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources