Tip: Finding a value in any of the columns in a SQL Server table

Few days ago one of my colleague working in another team in my office asked me one question that he have had one table named “PatientDetail” with 58 columns and he wanted to find value from all varchar columns. As a developer the first solution came in my mind was – check value in each column like this FirstName = ‘hello’ OR LastName = ‘hello’ OR…so on. One thing to note here is there are more than 50 varchar columns out of 58 columns. So your query looks something like this –

SELECT * FROM PatientDetail
    WHERE FirstName = 'hello'
	  OR LastName = 'hello'
	  OR Column3 = 'hello'
	  OR Column4 = 'hello'
	  Or Column5 = 'hello'
	  OR ... Column52 = 'hello'

Don’t you think that above query becomes too much lengthy if i write each column name here. So What is the preferred solution ?

Hmm Do you know that you can use column name in “IN Clause” in sql server!! Really ??

Yes, the above query you can rewrite like this –

SELECT * FROM PatientDetail
     WHERE 'hello' IN (FirstName, LastName, Column3, Column4, Column5,...)

Well, we just reverse the IN Clause! and this is perfectly valid statement for sql server. Try it yourself. Now the only problem here is we still have to write more than 50 columns in “IN” clause. So to overcome this problem, let’s convert the above query into dynamic sql.

-- *** Prepare comma seperated column list ***
DECLARE @ColumnNames VARCHAR(3000)

SELECT @ColumnNames = ISNULL(@ColumnNames + ', ' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME)) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PatientDetail'
	AND DATA_TYPE IN ('char', 'nvarchar', 'text', 'varchar')

--PRINT @ColumnNames

DECLARE @query VARCHAR(MAX)=
	'SELECT * FROM PatientDetail
	WHERE ''hello'' IN (' + @ColumnNames + ')'
	
EXEC SP_EXECUTESQL @query

That’s it. Hope you like this tip. If you know any other solution then post it in the comments. Thank you for reading this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s