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;
}
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.
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
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.
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?
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), ' ', '-')
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With