Generating Page Slugs in T-SQL!

I recently came across a situation where I needed to generate page Slugs from some 5,000,000+ records in a MS-SQL database. Unfortunately I didn’t have Remote Desktop or Telnet access to the SQL box which precluded me from running one of my normal C# or Powershell solutions, and the idea of doing all of the processing over the wire just didn’t appeal to me. So I decided the best course of action would be to use T-SQL to generate the Slugs for me.

After posting the question to StackOverflow and doing some searching on Google I came across Pinal Dave’s UDF_ParseAlphaChars function. Luckily it had most of the functionality I required, so it only required minor modifications to get the desired result.

The modified code is below.

CREATE FUNCTION [dbo].[UDF_GenerateSlug]
(
    @str VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN	
    DECLARE @IncCharLoc SMALLINT
    SET @str = LOWER(@str)
    SET @IncCharLoc = PATINDEX('%[^0-9a-z] %',@str)
    
    WHILE @IncCharLoc > 0
    BEGIN
    	SET @str = STUFF(@str,@IncCharLoc,1,'')
    	SET @IncCharLoc = PATINDEX('%[^0-9a-z] %',@str)
    END
    
    SET @str = REPLACE(@str,' ','-')
    RETURN @str
    END 
GO

First of all I need to leave spaces so they can be replaced with hyphens, and secondly I only require lower case characters. You may also notice that I’m doing a case transformation against the incoming string. This is due to the SQL database being setup with case insensitivity.

I would like to thank Pinal Dave for his original code.

About Jeremy

Jeremy is a Father of 3, Husband, overly opinionated Software Engineer, DevOps Practitioner, Baseball Coach and Professional Trouble Maker.

Jeremy is currently the Chief Trouble Maker at Lüp where he oversees engineering.
You can find him on Twitter and GitHub.