SQL Server "Codify" Function

I wrote yesterday about how I like to include a short, 2- to 5-character abbreviation code in my lookup tables.

Here's an excerpt from the article, Abbreviations in Lookup Tables:

BEFORE

Notice how much space the Industry column takes up.  If you didn't know any better, you would think that was the most important column on the form.

AFTER

The form below would be harder for a new user to understand, as the industry abbreviations will be unfamiliar to them.  But for a regular user, the condensed information would actually make this form easier to use.

Creating Abbreviations from an Existing Lookup Table

At the end of that article, I promised to provide a SQL Server function to generate these types of codes automatically.

To be clear, this function only provides a starting point for creating lookup table codes.  It won't do anything to prevent duplicate abbreviations.  Not every abbreviation it creates will be the best fit for the description it goes with.  It's main purpose is to jump start the process.

You (or your users) will still need to review the results and modify them as needed.

The Codify Function

The T-SQL Codify function that I adapted from this StackOverflow answer takes two arguments:

• The text you want to abbreviate
• The max number of characters you want returned

It follows a few simple rules:

• Use the first letter from each word
• Replace instances of 'and' with '&'
• Don't allow a trailing '&'
• If only one word, return first @codelen letters in the word

Here's the T-SQL:

/*****************************************************************
| This function creates an abbreviated code from a longer string
|   - Author: Mike Wolfe
|   - Source: https://nolongerset.com/sql-server-codify-function/
| 
| It follows these rules:
|     - Use the first letter from each word
|     - Replace instances of 'and' with '&'
|     - Don't allow a trailing '&'
|     - If only one word, return first @codelen letters in the word
|
| Adapted from: https://stackoverflow.com/a/9304623/154439
******************************************************************/
CREATE OR ALTER FUNCTION dbo.Codify (
    @txt nvarchar(2000),
    @codeLen int )
RETURNS nvarchar(2000) AS
        
BEGIN
    DECLARE @retval NVARCHAR(2000);
    DECLARE @str NVARCHAR(2000);

    SET @str=RTRIM(LTRIM(@txt));
    SET @retval=LEFT(@str,1);

    WHILE CHARINDEX(' ',@str,1)>0 AND LEN(@retval) < @codeLen BEGIN
        SET @str=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1)));
        IF UPPER(LEFT(@str, 3)) = 'AND'
            -- Use ampersand (&) instead of 'a' to replace 'And'
            SET @retval+='&'
        ELSE
            SET @retval+=LEFT(@str,1);

        -- Do not create a code with a trailing ampersand
        IF (LEN(@retval) = @codeLen) AND RIGHT(@retval,1) = '&'
            SET @retval = LEFT(@retval, @codeLen - 1)

    END

    IF Len(@retval) = 1
        SET @retval = LEFT(@txt,@codeLen);
    
    RETURN @retval;

END
GO 

Sample Usage

SELECT DISTINCT f.industry
 , dbo.Codify(f.industry, 2) AS [2-char Code]
 , dbo.Codify(f.industry, 3) AS [3-char Code]
 , dbo.Codify(f.industry, 4) AS [4-char Code]
 , dbo.Codify(f.industry, 5) AS [5-char Code]
 , dbo.Codify(f.industry, 6) AS [6-char Code]
FROM dbo.Fortune500 AS F

And here are the results of the above query:

Referenced articles

Abbreviations in Lookup Tables

One way to boost the signal-to-noise ratio on your continuous forms is to use abbreviations rather than full descriptions for lookup tables.

No Longer SetMike Wolfe

Images:

• Image by Darkmoon_Art from Pixabay
• Letter images by Michael Schwarzenberger from Pixabay