Creating SEO-friendly Slugs in SQL Server with to_slug Function

Are you a web developer or someone who manages content on a website? If so, you might be familiar with the concept of slugs. Slugs are user-friendly, URL-safe representations of strings that can be used in URLs to improve readability and search engine optimization. In this blog post, we'll explore a useful SQL Server function called to_slug that takes a string as input and generates a slug for use in URLs.

CREATE FUNCTION [dbo].[to_slug](@string NVARCHAR(MAX), @maxLength INT = 200, @separator NVARCHAR(1) = '-')
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @slug NVARCHAR(MAX)

    -- Step 1: Transliterate non-ASCII characters to ASCII
    DECLARE @charMapping TABLE (NonAscii NVARCHAR(10), Ascii NVARCHAR(10))
    INSERT INTO @charMapping (NonAscii, Ascii)
    VALUES
        (N'á', 'a'), (N'é', 'e'), (N'í', 'i'), (N'ó', 'o'), (N'ú', 'u'), (N'ñ', 'n'), (N'ç', 'c'),
        (N'À', 'A'), (N'È', 'E'), (N'Ì', 'I'), (N'Ò', 'O'), (N'Ù', 'U')

    SET @slug = @string
    SELECT @slug = REPLACE(@slug, NonAscii, Ascii)
    FROM @charMapping

    -- Step 2: Remove non-alphanumeric characters and duplicate spaces
    SET @slug = LOWER(REPLACE(REPLACE(REPLACE(@slug, ' ', @separator), NCHAR(160), ''), '[^a-z0-9-]', ''))

    -- Step 3: Trim the slug to the maximum length
    SET @slug = LEFT(@slug, @maxLength)

    -- Step 4: Remove any trailing separator characters
    WHILE RIGHT(@slug, 1) = @separator
    BEGIN
        SET @slug = LEFT(@slug, LEN(@slug) - 1)
    END

    -- Step 5: Handle empty or blank slugs
    IF (LEN(@slug) = 0)
    BEGIN
        SET @slug = 'str_0'
    END

    RETURN @slug
END

GO

Introducing the to_slug Function

The to_slug function is a user-defined scalar function in SQL Server that helps transform a given string into a clean and SEO-friendly slug. It follows a series of steps to achieve this transformation:

1. Step 1: Transliteration of Non-ASCII Characters

   The first step in creating a slug is to transliterate non-ASCII characters into their closest ASCII equivalents. For example, characters like "á," "é," or "ñ" will be converted to "a," "e," and "n," respectively. This ensures that special characters are simplified, making the slug more accessible and easier to read.

2. Step 2: Removing Non-Alphanumeric Characters and Duplicate Spaces

   After the transliteration, the function proceeds to remove any non-alphanumeric characters from the string, leaving only letters and numbers. Additionally, it replaces duplicate spaces with a single separator character (by default, a hyphen '-'). This step further cleans up the string, making it more suitable for URLs.

3. Step 3: Limiting Slug Length

   To prevent excessively long slugs, the to_slug function allows you to specify a maximum length for the generated slug. If the resulting slug exceeds this length, it will be truncated to fit within the limit.

4. Step 4: Trimming Trailing Separator Characters

   In some cases, the removal of non-alphanumeric characters and duplicate spaces might leave trailing separator characters at the end of the slug. To ensure a clean and concise URL representation, the function removes any such trailing separator characters.

5. Step 5: Handling Empty or Blank Slugs

   In the event that the input string is empty or consists only of spaces, the function ensures that it generates a default slug, such as "str_0". This step is crucial to avoid creating invalid or misleading URLs.

Using the to_slug Function

The to_slug function accepts three parameters:

1. @string (NVARCHAR(MAX)): The input string that you want to convert into a slug.
2. @maxLength (INT, optional, default = 200): The maximum length of the slug. If the generated slug exceeds this length, it will be truncated.
3. @separator (NVARCHAR(1), optional, default = '-'): The separator character used to replace spaces and non-alphanumeric characters in the slug.

You can easily integrate the to_slug function into your SQL Server database by copying and executing the provided SQL code.

Example Usage

Let's look at a few examples to see how the to_slug function works:

• Basic Usage:
SELECT dbo.to_slug('Hello World!') -- Output: 'hello-world'
• Custom Max Length and Separator:
SELECT dbo.to_slug('This is a long sentence that needs to be shortened for a slug!', 15, '_') -- Output: 'this-is-a-long'
• Handling Non-ASCII Characters:
SELECT dbo.to_slug('Héllo Wórld', 10) -- Output: 'hello-world'

Conclusion

Creating user-friendly and SEO-optimized URLs is essential for any web application or website. With the to_slug function in SQL Server, you now have a convenient tool to generate slugs from strings efficiently. By following the steps outlined in this blog post, you can easily create clean, readable, and URL-safe slugs that enhance the overall user experience.

Remember to adapt the function to fit your specific needs, such as adjusting the maximum length or separator character to align with your project requirements. Enjoy improved SEO and user-friendly URLs with the to_slug function!