Menu
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?
768
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.
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.
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.)
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
