SQL Server:字符串操纵以写出SEO友好标题

Question

我需要按照给我的一些规则来以seo frienly格式编写歌曲的标题。

最大lentgh是52个字符。目前，对于以CL开头的记录发生了截断，并且与一组关联ID有关。

我要应用的其他规则是：

• 如果seo_friendly_title以a' - '将其删除。
• 如果截断在单词或空间的中间结束，然后移动到下一个空间（下一个单词的结尾）。

我认为我必须添加嵌套的CASE语句来完成此操作，但我不知道在哪里添加此新检查。

我想寻求您完成此算法的帮助，希望有人可以分享他们的经验并展示/解释如何做到这一点。

非常感谢。

这是我当前的SQL查询：

SELECT
    [sfwt].[seo_friendly_title]
,   CASE    
        WHEN [sfwt].[pf_id] LIKE 'CL%' AND [pd].[AssociationID] IN ( 1, 3, 4 ) THEN 
            LEFT([sfwt].[seo_friendly_title], 52)
    END [seo_final_title]
FROM
    [dbo].[SEOFriendly_WorkingTable] [sfwt]
INNER JOIN [dbo].[ProductData] [pd]
    ON [pd].[ProductID] = [sfwt].[pf_id]
WHERE [sfwt].[pf_id] LIKE 'CL%'
ORDER BY    
    [sfwt].[pf_id];

和输出：

seo_friendly_title  seo_final_title
prelude-no-5-for-trumpet-and-piano-johann-sebastian-bach    prelude-no-5-for-trumpet-and-piano-johann-sebastian-
prelude-no-8-for-trumpet-and-piano-johann-sebastian-bach    prelude-no-8-for-trumpet-and-piano-johann-sebastian-
highlights-from-the-lord-of-the-rings-the-return-of-the-king    highlights-from-the-lord-of-the-rings-the-return-of-
air-ecossais-spirituoso-e-marciale-opus-107-no-10-f-instrument-piano    air-ecossais-spirituoso-e-marciale-opus-107-no-10-f-
air-ecossais-spirituoso-e-marciale-opus-107-no-10-c-instrument-piano    air-ecossais-spirituoso-e-marciale-opus-107-no-10-c-
air-de-la-petite-russie-opus-107-no-3-bb-instrument-piano   air-de-la-petite-russie-opus-107-no-3-bb-instrument-
air-de-la-petite-russie-opus-107-no-3-eb-instrument-piano   air-de-la-petite-russie-opus-107-no-3-eb-instrument-
shell-be-coming-round-the-mountain-c-instrument-and-piano   shell-be-coming-round-the-mountain-c-instrument-and-
shell-be-coming-round-the-mountain-f-instrument-and-piano   shell-be-coming-round-the-mountain-f-instrument-and-
9-ecossaises-from-38-waltzer-landler-und-ecossaisen-op-18   9-ecossaises-from-38-waltzer-landler-und-ecossaisen-

Answer 1

您去这里。更新了09/27

  SELECT
    [sfwt].[seo_friendly_title]
,   CASE    
        WHEN [sfwt].[pf_id] LIKE 'CL%' AND [pd].[AssociationID] IN ( 1, 3, 4 ) and LEFT([sfwt].[seo_friendly_title], 52) LIKE '%-' THEN LEFT([sfwt].[seo_friendly_title], 51)
        WHEN [sfwt].[pf_id] LIKE 'CL%' AND [pd].[AssociationID] IN ( 1, 3, 4 ) THEN LEFT([sfwt].[seo_friendly_title], 52)
        WHEN [sfwt].[pf_id] LIKE 'CL%' AND [pd].[AssociationID] IN ( 1, 3, 4 ) and LEFT([sfwt].[seo_friendly_title], 52) NOT like '%-' 
            and left(Replace([sfwt].[seo_friendly_title],LEFT([sfwt].[seo_friendly_title], 52),''),1) <> '-'
            and Charindex('-',Replace([sfwt].[seo_friendly_title],LEFT([sfwt].[seo_friendly_title], 52),'') = 0 then [sfwt].[seo_friendly_title]
        WHEN [sfwt].[pf_id] LIKE 'CL%' AND [pd].[AssociationID] IN ( 1, 3, 4 ) and LEFT([sfwt].[seo_friendly_title], 52) NOT like '%-' 
            and left(Replace([sfwt].[seo_friendly_title],LEFT([sfwt].[seo_friendly_title], 52),''),1) <> '-'
            and Charindex('-',Replace([sfwt].[seo_friendly_title],LEFT([sfwt].[seo_friendly_title], 52),'') != 0 then LEFT([sfwt].[seo_friendly_title], 51 + Charindex('-',Replace([sfwt].[seo_friendly_title],LEFT([sfwt].[seo_friendly_title], 52),'')))
    END [seo_final_title]
FROM
    [dbo].[SEOFriendly_WorkingTable] [sfwt]
INNER JOIN [dbo].[ProductData] [pd]
    ON [pd].[ProductID] = [sfwt].[pf_id]
WHERE [sfwt].[pf_id] LIKE 'CL%'
ORDER BY    
    [sfwt].[pf_id];

Answer 2

您可以尝试为1点1空间（下一个单词的结尾）。"您可以分享示例吗？

SELECT
    [sfwt].[seo_friendly_title]
,   CASE    
        WHEN [sfwt].[pf_id] LIKE 'CL%' AND [pd].[AssociationID] IN ( 1, 3, 4 ) and LEFT([sfwt].[seo_friendly_title], 52) LIKE '%-' THEN LEFT([sfwt].[seo_friendly_title], 51)
        WHEN [sfwt].[pf_id] LIKE 'CL%' AND [pd].[AssociationID] IN ( 1, 3, 4 ) THEN LEFT([sfwt].[seo_friendly_title], 52)
    END [seo_final_title]
FROM
    [dbo].[SEOFriendly_WorkingTable] [sfwt]
INNER JOIN [dbo].[ProductData] [pd]
    ON [pd].[ProductID] = [sfwt].[pf_id]
WHERE [sfwt].[pf_id] LIKE 'CL%'
ORDER BY    
    [sfwt].[pf_id];