Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

String.IsNullOrEmpty like function for VARCHARs in SQL?

Tags:

Say I've got a function or stored procedure that takes in several VARCHAR parameters. I've gotten tired of writing SQL like this to test if these parameters have a value:

IF @SomeVarcharParm IS NOT NULL AND LEN(@SomeVarcharParm) > 0 BEGIN     -- do stuff END 

There's gotta be a better way to do this. Isn't there?

like image 768
AJ. Avatar asked Jul 28 '11 17:07

AJ.


People also ask

How do I match a string in SQL?

SQL Pattern Matching : It is used for searching a string or a sub-string to find certain character or group of characters from a string. We can use LIKE Operator of SQL to search sub-string. The LIKE operator is used with the WHERE Clause to search a pattern in string of column.

How do you select non empty values in SQL?

Select non-empty column values using NOT IS NULL and TRIM() function. The syntax is as follows. SELECT * FROM yourTableName WHERE yourColumnName IS NOT NULL AND TRIM(yourColumnName) <> ' '; You can select non-empty value as well as whitespace from column using the same TRIM() function.


1 Answers

You can do ISNULL(@SomeVarcharParam, '') <> '' or you can create a UDF that returns a bit:

create function dbo.IsNullOrEmpty(@x varchar(max)) returns bit as BEGIN IF @SomeVarcharParm IS NOT NULL AND LEN(@SomeVarcharParm) > 0     RETURN 0 ELSE     RETURN 1 END 

And call that using IF NOT dbo.IsNullOrEmpty(@SomeVarcharParam) BEGIN ...

Keep in mind that when calling a UDF, you MUST prefix the owner of it (here, dbo.)

like image 86
Derek Kromm Avatar answered Oct 03 '22 12:10

Derek Kromm