CODEWITHSUNDEEP
sql-serverregext-sql
Anton K
Anton K

Reputation: 49

Regular expression to find both non-null and NULL values

My stored procedure is using regular expression as a parameter to filter a set of rows:

DECLARE @filter = '%'

SELECT * 
FROM TableA 
WHERE ColumnA LIKE @filter

Value '%' extracts all non-null values of ColumnA. However I need to extract both non-null AND null values.

The idea of using second condition (and ColumnA is NULL) is not suitable as the filter shall come as a single parameter.

It would be great to find if there any regex expression which can cover NULL values

Upvotes: 0

Views: 714

Answers (2)

Thom A
Thom A

Reputation: 95659

SQL Server doesn't support Regex, only it's own (more limited) pattern matching.

Normally I wouldn't recommend this answer, however, as you have a leading wild card already performance will be out the window. Assuming you want '%' to return every row, you could do:

DECLARE @Filter = '%';

SELECT *
FROM TableA
WHERE ISNULL(ColumnA,'|') LIKE @Filter;

Note, however, that if you might not be using leading wild cards this is a really bad idea; it'll kill performance. If that is the case, then use Lukasz's solution, and not this one.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

You could use OR condition:

DECLARE @filter = '%'
SELECT * FROM TableA 
where ColumnA like @filter OR @filter = '%'

Upvotes: 1

Related Questions