How to extract specific text from a String using SSMS?

Question

I have a string which has the volume information in terms of ML and LTR. How do I extract it from the below field?

Text:

BRWN SPRTS 50 ML DRK RM AL N N SLR JRR

BRWN SPRTS DRK RM AL N N SLR JRR 700ML

700 ML DRK RM AL N N SNG-SM ALL OTHR

SPRTS DRK RM AL 1.5 LTR N N TH-DPP-SHR

N N TH-KRKN TH-KRKN-BLCK-SPCD-RM 3.5 LTR

N N TH-KRKN 50 ML TH-KRKN-BLCK-SPCD-RM

I want the result to be like this:

50ML

700ML

700ML

1.5LTR

3.5LTR

50ML

You can use RegEx. Are you using C# to communicate with the DB? Here's an expression: `((?:[0-9]+\.)?(?:[0-9]+))\s?(ML|LTR)` — Furkan Kambay, Oct 26 '18 at 09:01
You could either register an assembly (.dll) with Regex methods or try to use PatIndex. Those would be the easy choices, a more complex one would be creating an scalar function. — Cleptus, Oct 26 '18 at 09:05
Possible duplicate of [SQL Server Regular expressions in T-SQL](https://stackoverflow.com/questions/194652/sql-server-regular-expressions-in-t-sql) — Cleptus, Oct 26 '18 at 09:05

score 0 · Answer 1 · answered Oct 26 '18 at 09:40

CREATE TABLE #TABLE1
    ([CLOUMNN] VARCHAR(40))
;

INSERT INTO #TABLE1
    ([CLOUMNN])
VALUES
    ('BRWN SPRTS 50 ML DRK RM AL N N SLR JRR'),
    ('BRWN SPRTS DRK RM AL N N SLR JRR 700ML'),
    ('700 ML DRK RM AL N N SNG-SM ALL OTHR'),
    ('SPRTS DRK RM AL 1.5 LTR N N TH-DPP-SHR'),
    ('N N TH-KRKN TH-KRKN-BLCK-SPCD-RM 3.5 LTR'),
    ('N N TH-KRKN 50 ML TH-KRKN-BLCK-SPCD-RM')
;
CREATE FUNCTION DBO.UDF_GETNUMERIC_1  
(@STRALPHANUMERIC VARCHAR(256))  
RETURNS VARCHAR(256)  
AS  
BEGIN  
DECLARE @INTALPHA INT  
SET @INTALPHA = PATINDEX('%[^0-9.]%', @STRALPHANUMERIC)  
BEGIN  
WHILE @INTALPHA > 0  
BEGIN  
SET @STRALPHANUMERIC = STUFF(@STRALPHANUMERIC, @INTALPHA, 1, '' )  
SET @INTALPHA = PATINDEX('%[^0-9.]%', @STRALPHANUMERIC )  
END  
END  
RETURN ISNULL(@STRALPHANUMERIC,0)  
END  

SELECT CONCAT([STRING],CASE WHEN [CLOUMNN]>0 THEN 'ML' ELSE 'LTR' END) 
FROM (SELECT DBO.UDF_GETNUMERIC_1([CLOUMNN])[STRING] ,CHARINDEX('ML',CLOUMNN)
 CLOUMNN FROM #TABLE1)A

OUTPUT

50ML
700ML
700ML
1.5LTR
3.5LTR
50ML

score 0 · Answer 2 · answered Oct 26 '18 at 16:01

This should give you the results you're looking for without whaing to do any looping...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
    CharacterString VARCHAR(100) NOT NULL 
    );
INSERT #TestData(CharacterString) VALUES
    ('BRWN SPRTS 50 ML DRK RM AL N N SLR JRR'),
    ('BRWN SPRTS DRK RM AL N N SLR JRR 700ML'),
    ('700 ML DRK RM AL N N SNG-SM ALL OTHR'),
    ('SPRTS DRK RM AL 1.5 LTR N N TH-DPP-SHR'),
    ('N N TH-KRKN TH-KRKN-BLCK-SPCD-RM 3.5 LTR'),
    ('N N TH-KRKN 50 ML TH-KRKN-BLCK-SPCD-RM');

--==================================================================

SELECT 
    *,
    Output = REPLACE(ISNULL(
        SUBSTRING(td.CharacterString, ns.num_ml, se.ml_end - ns.num_ml),
        SUBSTRING(td.CharacterString, ns.num_ltr, se.ltr_end - ns.num_ltr)
        ), ' ', '')
FROM
    #TestData td
    CROSS APPLY ( VALUES (
        NULLIF(PATINDEX('%[0-9]%ML%', td.CharacterString), 0),
        NULLIF(PATINDEX('%[0-9]%LTR%', td.CharacterString), 0)
        ) ) ns (num_ml, num_ltr)
    CROSS APPLY ( VALUES (
        CHARINDEX('ML', td.CharacterString, ns.num_ml) + 2,
        CHARINDEX('LTR', td.CharacterString, ns.num_ltr) + 3
        ) ) se (ml_end, ltr_end);

Reults...

Output
------------
50ML
700ML
700ML
1.5LTR
3.5LTR
50ML

How to extract specific text from a String using SSMS?

2 Answers2