CODEWITHSUNDEEP
sqlt-sqlnullsql-server-2012
Tony Clifton
Tony Clifton

Reputation: 703

TSQL - dynamic WHERE clause to check for NULL for certain columns

Using Sql Server 2012 I want to query a table to only fetch rows where certain columns are not null or don't contain an empty string.

The columns I need to check for null and ' ' all start with either col_as or col_m followed by two digits.

At the moment I write where col_as01 is not null or .... which becomes difficult to maintain due to the quantity of columns I have to check.

Is there a more elegant way to do this? Some kind of looping?

I also use ISNULL(NULLIF([col_as01], ''), Null) AS [col_as01] in the select stmt to get rid of the empty string values.

thank you for your help.

Upvotes: 0

Views: 1351

Answers (3)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

You should fill in the blanks.

select 
    @myWhereString =stuff((select 'or isnull('+COLUMN_NAME+','''') = '''' ' as [text()] 
                       from Primebet.INFORMATION_SCHEMA.COLUMNS 
                       where TABLE_NAME = 'YourTable'
                           and (column_name like 'col_as%'
                                or
                                column_name like 'col_m%')
                       for xml path('')),1,3,'')
set @myWhereString ='rest of your query'+ @myWhereString

exec executesql with your query

Upvotes: 2

gvee
gvee

Reputation: 17161

Simple as this:

WHERE  col_as01 > ''

Upvotes: 0

DrCopyPaste
DrCopyPaste

Reputation: 4117

You can use something like this

WHERE DATALENGTH(col_as01) > 0

That will implicitly exclude null values, and the length greater 0 will guarantee you to retrieve non empty strings.

PS: You could also use LEN instead of DATALENGTH but that will trim spaces in your string at the beginning and end so you would not get values that only contain spaces then.

Upvotes: 0

Related Questions