Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stuck on Substring/Left/Charindex Problem with Description Expand / Collapse
Author
Message
Posted Friday, February 07, 2014 11:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:05 PM
Points: 387, Visits: 418
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]

Post #1539306
Posted Friday, February 07, 2014 12:34 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:05 PM
Points: 387, Visits: 418
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]

Post #1539353
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse