Some Useful SQL Functions
I’m not sure why it is, but Transact-SQL seems to be shy a few important functions that are useful for parsing irregular data. Sure, they include the isNull function to let you substitute something useful for a NULL value, but they don’t provide good negation functions for the opposite case (at least not that I could find). I’ve invented a few new ones that provide some useful functionality and extend your ability to query against irregular data.
Here they are:
- IsEmpty(@argument) – Takes a string value and tests to see if it is null or contains an empty string. If it is not empty, it returns the argument, otherwise it returns NULL. Use this inside an ifNull() test on fields that may contain nulls or empty strings.
- IfEmpty(@argument, @default) – Similar to IsEmpty(), this one lets you pass in a default value to use if the argument is empty. It’s basically the same as IsNull(), except that it works with NULL and empty strings.
- NotEmpty(@argument, @default) – This is the negation case that I found missing. It returns the default value if the tested value contains something, otherwise an empty string. Great for things like zip-plus 4 on zip codes, allowing you to print the dash only if the plus 4 field has a value in it.
Limitations:
These functions were designed mainly for returning formatted text for reporting or rendering into HTML or XSLT stylesheets, so they assume string arguments. You will have to coerce your values into strings to use them, or write similar functions for other data types.
The source:
CREATE FUNCTION IsEmpty
(
@argument AS varchar(8000)
)
RETURNS varchar(8000)
BEGIN
if @argument is null return null
if len(@argument) = 0
begin
set @argument=null
end
return @argument
END
CREATE FUNCTION IfEmpty
(
@argument AS varchar(8000),
@default as varchar(8000)
)
RETURNS varchar(8000)
BEGIN
if dbo.isEmpty(@argument) is null
begin
return @default
end
return @argument
END
CREATE FUNCTION NotEmpty
(
@argument AS varchar(800),
@default as varchar(800)
)
RETURNS varchar(8000)
BEGIN
if dbo.isEmpty(@argument) is not null
begin
return @default
end
return ''
END