Stuck on Substring/Left/Charindex Problem with Description

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

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply