seo Question posted in
Some basic information to get you started with this can be found here.

SQL Server : String manipulation to write out SEO friendly titles

I need to write the Titles of songs in a SEO frienly format, following a few rules given to me.

The maximum lentgh is 52 characters. Currently the truncation occurs for records that begin with a CL, and that pertain to a group of association ids.

The additional rules I have to apply are:

  • If the seo_friendly_title ends with a ‘-‘ remove it.
  • If the
    truncation ends in the middle of a word, or a space, then move to the
    next space (end of next word).

I think I have to add nested CASE statement(s) to accomplish this, but I don’t know where to add this new checks.

I would like to ask for your help in completing this algorithm, hopefully someone can share their experience and show/ explain how to do this.

Many thanks.

Here is my current SQL query:

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];

And the output:

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-
Tags:

2

Answers


  1. 0

    You can try this for point 1 but I am not clear for point number two which is “If the truncation ends in the middle of a word, or a space, then move to the next space (end of next word).” can you share the example?

    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];
    Login or Signup to reply.
  2. 0

    Here you go. Updated 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];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search