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

How can I return the first N number of characters ending with a complete word from a dash-delimited string? – SEO

I need a function that outputs first N number of characters in a dash-delimited input string.

Requirements:

  1. If N drops in the middle of a word, include the last word in the output even if total gets more than N

  2. If the output ends with “-” AND LEN(output) == N , then include next word

Example: (N = 70)

declare @Text varchar(1000) = 'this-is-product-url-prepared-for-better-Google-Search-Engnine-SEO-totalLength-should-be-70-characters'

I have already taken the first 70 characters, which results in the following (I remove ending dash – in a separate step not included here):

SELECT LEFT(@Text + '-', CHARINDEX('-',@Text, 70))
this-is-product-url-prepared-for-better-Google-Search-Engnine-SEO-tot

I need a function that returns the following in this case:

this-is-product-url-prepared-for-better-Google-Search-Engnine-SEO-totalLength

Any solutions will be appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks @LukStorms

    I achieved this by the following statement:

    LEFT(@Text,CHARINDEX('-',@Text+'-',70)-1)

  2. 0

    UPDATED ANSWER: Fixed your expression

    This will work and is based on what @Lukstroms posted; his does not handle your second requirement (when @N is the position of a hyphen.)

    SELECT SUBSTRING(@Text,1,CHARINDEX('-',@Text,@N+1)-1);

    PREVIOUS ANSWER:

    Here’s how you could do something like this using ngrams8k This will handle situations where @N represents the middle of the word or a dash -.

    DECLARE 
  @Text VARCHAR(1000) = 'this-is-product-url-prepared-for-better-Google-Search-Engnine-SEO-totalLength-should-be-70-characters',
  @N    INT           = 70;

SELECT SUBSTRING(@Text,0,MAX(ng.nxt))
FROM 
(
  SELECT ng.position, nxt = LEAD(ng.Position,1) OVER (ORDER BY ng.Position), ng.Token
  FROM   samd.ngrams8k(@Text,1) AS ng
  WHERE  ng.Token = '-'  
) AS ng
WHERE ng.Position <= @N;

    FINAL UPDATE (NOTE ABOUT N-GRAMS)

    As Martin correctly mentioned, the N-Grams solution is overly complex but I was in a rush and couldn’t fix the OP’s original CHARINDEX expression. That said, ngrams8k solution is nasty fast allows a bunch of flexibility.

    For example, let’s say the requirement included an upper and lower bound parameter; e.g. we needed everything between the first hyphen higher than @Low and the last hyphen lower than @High. This can be handled using a minor tweak to the answer above. 

    DECLARE
  @Text VARCHAR(1000) = 'this-is-product-url-prepared-for-better-Google-Search-Engnine-SEO-totalLength-should-be-70-characters',
  @Low  INT           = 16,
  @high INT           = 70;

SELECT NewString = SUBSTRING(@Text,MIN(ng.position)+1, MAX(ng.nxt)-MIN(ng.position)-1)
FROM 
(
  SELECT ng.position, nxt=LEAD(ng.Position,1) OVER (ORDER BY ng.Position)
  FROM   samd.ngrams8k(@Text,1) AS ng
  WHERE  ng.Token = '-'  
) AS ng
WHERE ng.Position <= @high AND ng.Position > @low;

    Returns:

    prepared-for-better-Google-Search-Engnine-SEO-totalLength
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search