The W Blog

Little Known Transact-SQL Stars

Transact-SQL (T-SQL from here on) has many unheralded functions that can make your life easier if you only know of their existence.  Today I'm talking about the NULLIF, QUOTENAME, and PARSENAME functions.  These functions are great for string manipulation and handle some common coding problems.

We will start with NULLIF.  This function allows you to examine an input value for a given expression, returning a NULL if the value is found.  Here's an example:

DECLARE @var VARCHAR(50);
SET @var = '';

SELECT NULLIF(@var, '') AS [@var];

If you run the code in SQL Server Management Studio (SSMS from here on) you will see the output of the query is NULL.  This can be very handy in data cleansing activities where you wish to remove empty strings.

Up next is the QUOTENAME function.  I find that I use this quite a bit in creating dynamic SQL as well as scripting out data from tables for insert scripts.  QUOTENAME takes a character string followed by a quote character.  You may use single quotation marks, double quotation marks, or single brackets.  If no quote character is passed then single brackets are used.

Here's an example:

DECLARE @var2 VARCHAR(50);
SET @var2 = 'Whitney';

SELECT 
    QUOTENAME(@var2, '''') AS [SingleQuotes]
    ,QUOTENAME(@var2, '""') AS [DoubleQuotes]
    ,QUOTENAME(@var2) AS [Brackets];

Running the code in SSMS returns each of the allowed quote characters applied to the variable.

Finally, the PARSENAME function can be used to return the specified parts of an object name. The function takes a string input for the object name and an integer value for the object piece you wish to return. 

The integer values are:

1 = Object name

2 = Schema name

3 = Database name

4 = Server name

The function does not validate the existence of the object but merely looks to return the values to you.

Here's an example:

DECLARE @FullyQualifiedName VARCHAR(100);
SET @FullyQualifiedName = 'MyServer.MyDatabase.MySchema.MyObject';

SELECT
    PARSENAME(@FullyQualifiedName, 1) AS ObjectName
    ,PARSENAME(@FullyQualifiedName, 2) AS SchemaName
    ,PARSENAME(@FullyQualifiedName, 3) AS DatabaseName
    ,PARSENAME(@FullyQualifiedName, 4) AS ServerName;

A quick trip back to SSMS and we see the query returned the pieces of the object name as described.  An additional, if perhaps unintended, use of the PARSENAME function is for breaking apart IP values.

In this example we need each IP octet returned as a separate value.  Here's the code:

DECLARE @IP VARCHAR(20);
SET @IP = '10.11.12.13';

SELECT 
    PARSENAME(@IP, 4) [IPOctet1]
    ,PARSENAME(@IP, 3) [IPOctet2]
    ,PARSENAME(@IP, 2) [IPOctet3]
    ,PARSENAME(@IP, 1) [IPOctet4];

For use with IP addresses you will need to reverse the output order of the IP as the PARSENAME function is looking to work from right to left.

And there you have it, three extremely useful functions that you may or may not have had in your T-SQL arsenal.  Hopefully these will help you out in the future.