Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL function for generating slugs?

Tags:

tsql

slug

Quick check to see if anyone has or knows of a T-SQL function capable of generating slugs from a given nvarchar input. i.e;

"Hello World" > "hello-world"
"This is a test" > "this-is-a-test"

I have a C# function that I normally use for these purposes, but in this case I have a large amount of data to parse and turn into slugs, so it makes more sense to do it on the SQL Server rather than have to transfer data over the wire.

As an aside, I don't have Remote Desktop access to the box so I can't run code (.net, Powershell etc) against it

Thanks in advance.

EDIT: As per request, here's the function I generally use to generate slugs:

public static string GenerateSlug(string n, int maxLength)
{
    string s = n.ToLower();                
    s = Regex.Replace(s, @"[^a-z0-9s-]", "");              
    s = Regex.Replace(s, @"[s-]+", " ").Trim();             
    s = s.Substring(0, s.Length <= maxLength ? s.Length : maxLength).Trim();             
    s = Regex.Replace(s, @"s", "-"); 
    return s;
}
like image 253
Jeremy Cade Avatar asked Jun 21 '10 06:06

Jeremy Cade


People also ask

What is a user defined function in SQL Server?

A SQL Server user-defined function (Transact-SQL function) is useful when you want to keep one or more sql statements in the same block, this way the function can be reused. Scalar functions. User-defined scalar functions return a single data value.

How to create a function in T-SQL?

T-SQL Create Function 1. Create Function Example. CREATE FUNCTION CtrAmount ( @Ctr_Id int(10) ) RETURNS MONEY AS BEGIN DECLARE @CtrPrice MONEY SELECT @CtrPrice = SUM(amount) FROM Contracts WHERE contract_id = @Ctr_Id RETURN(@CtrPrice) END GO SELECT * FROM CtrAmount(345) GO 2. Create Function Example

What is an aggregate function in SQL?

An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT (*), aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

What are the types of functions in SQL Server?

Scalar functions. User-defined scalar functions return a single data value. The return type can be any data type (most used: numeric, date & time, string) except text, ntext, image, cursor, and timestamp. Table-valued functions. User-defined table-valued functions return a table data type. How to create a function with T-SQL?


2 Answers

You can use LOWER and REPLACE to do this:

SELECT REPLACE(LOWER(origString), ' ', '-')
FROM myTable

For wholesale update of the column (the code sets the slug column according to the value of the origString column:

UPDATE myTable
SET slug = REPLACE(LOWER(origString), ' ', '-')
like image 163
Oded Avatar answered Sep 18 '22 04:09

Oded


This is what I've come up with as a solution. Feel free to fix / modify where needed.

I should mention that the database I'm currently developing against is case insensitive hence the LOWER(@str).

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

Mention to: http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/ for the original code.

like image 27
Jeremy Cade Avatar answered Sep 21 '22 04:09

Jeremy Cade