It uses sename or productname and removes all specials caracters to create a clean seoFileName.
create function dbo.RemoveSpecialChars (@s varchar(1024)) returns varchar(1024)
with schemabinding
begin
if @s is null
return null
declare @s2 varchar(1024)
set @s2 = ''
declare @l int
set @l = len(@s)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@s, @p, 1))
if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c = 45
set @s2 = @s2 + char(@c)
set @p = @p + 1
end
if len(@s2) = 0
return null
return @s2
end
GO
DECLARE cur CURSOR
FOR SELECT
Product_Picture_Mapping.PictureId,
CASE WHEN Product.SeName = '' OR Product.SeName IS NULL THEN Product.Name ELSE Product.SeName END
FROM
Product_Picture_Mapping INNER JOIN
Product ON Product_Picture_Mapping.ProductId = Product.Id
DECLARE @name nvarchar(300);
DECLARE @id int;
OPEN cur
FETCH NEXT FROM cur INTO @id, @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @seourl nvarchar(300)
--Removes diacritics
SET @seourl =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(@name COLLATE Latin1_General_CI_AI
,'a','a'),'b','b'),'c','c'),'d','d'),'e','e'),'f', 'f'),'g','g'),'h','h'),'i','i'),'j','j'),'k','k'),'l', 'l')
,'m','m'),'n','n'),'o','o'),'p','p'),'q','q'),'r', 'r'),'s','s'),'t','t'),'u','u'),'v','v'),'w','w'),'x', 'x')
,'y','y'),'z','z')
SET @seourl = REPLACE(@seourl, ' ', '-');--Replaces spaces with -
SET @seourl = dbo.RemoveSpecialChars(@seourl);--Removes special chars
SET @seourl = REPLACE(@seourl, '--', '-');
SET @seourl = REPLACE(@seourl, '--', '-');--Repeat to remove series like ----
SET @seourl = REPLACE(@seourl, '--', '-');--Repeat to remove series like ----
PRINT @seourl
UPDATE dbo.Picture
SET SeoFilename = @seourl
WHERE Id = @id
END
FETCH NEXT FROM cur INTO @id, @name
END
CLOSE cur
DEALLOCATE cur
GO
drop function dbo.RemoveSpecialChars