Stuck on Substring/Left/Charindex Problem with Description

• SQL_Enthusiast

  Hall of Fame

  Points: 3805

  More actions

  February 7, 2014 at 11:03 am

  #144849

  This works for some items and not others. I can't explain it.

  What I need is value of 20, 15, or 10 from the description. So... stop at the first space after NAME (' ') and reverse stop at the '%'. Return the value between the first space and the %.

  CREATE TABLE TestSubstring

  (

  DESCRIPTION VARCHAR(255)

  );

  INSERT INTO TestSubstring

  SELECT

  'NAME 20% 1000ML ABC (LONG_NAME) 6/CASE';

  INSERT INTO TestSubstring

  SELECT

  'NAME 15% 500ML ABC (LONG_NAME)12/CASE';

  INSERT INTO TestSubstring

  SELECT

  'NAME 10% 250ML ABC (LONG_NAME)10/CASE';

  SELECT

  *

  FROM

  [dbo].[TestSubstring] AS ts

  Here is some code that works on one type of item, but it doesnt work on this type... I'm lost.

  SELECT

  SUBSTRING([ts].[DESCRIPTION], CHARINDEX(' ', [ts].[DESCRIPTION]) + 1,

  LEN([ts].[DESCRIPTION]) - CHARINDEX(' ', [ts].[DESCRIPTION]) - CHARINDEX('%', [ts].[DESCRIPTION]) - 2) AS [STRGH]

  FROM

  [dbo].[TableTest] AS [ts]

• SQL_Enthusiast

  Hall of Fame

  Points: 3805

  More actions

  February 7, 2014 at 12:34 pm

  #1687201

  In case you were wondering how, I figured it out... 😀

  CASE WHEN PATINDEX('%[^0-9]%', [ts].[DESCRIPTION]) > 0

  THEN LEFT(SUBSTRING([ts].[DESCRIPTION], PATINDEX('%[0-9]%', [ts].[DESCRIPTION]),

  CHARINDEX('%', [ts].[DESCRIPTION] + SPACE(1), CHARINDEX('%', [ts].[DESCRIPTION]))), 2)

  ELSE [ts].[DESCRIPTION]

  END AS [STRGH]