CODEWITHSUNDEEP
sqlsql-server
Aura
Aura

Reputation: 1307

SQL Select everything after numeric character

I have a following text in column:

ABC XYZ 100 MG PL/2 ML ABCD

I would like to extract everything after number and before ML

Expected output:

100 MG PL/2 ML

I tried RIGHT with PATINDEX but it is displaying the whole string after numeric data like following:

Select RIGHT(col, PATINDEX('%[^A-Z] %', col))
From table

Output obtained:

100 MG PL/2 ML ABCD

Can anyone suggest me how to extract this data?

Upvotes: 4

Views: 3428

Answers (3)

Shawn
Shawn

Reputation: 4786

You can go with

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t1 ( col varchar(50) ) ;

INSERT INTO t1 (col)
VALUES 
      ( 'ABC XYZ 100 MG PL/2 ML ABCD' ) 
    , ( 'ABC XYZ 99.9 MG PL/2.5 ML ABCD' )
    , ( 'ABCXYZ 10 mg pl/2 l abcdefghijklmn' )
;

Query 1:

/* 
    This extracts a substring from col, beginning at the 1st digit
    after a space, and ending at the length of the whole string minus
    the position of the last space minus the position of the 1st digit
    after a space (the first throwaway substring). 
*/

SELECT SUBSTRING( col, 
                  PATINDEX('% [^A-Z]%', col) /* ID 1st digit after space */
                  , LEN(col) /* length of full string */
                    - CHARINDEX(' ',REVERSE(col)) /* identify the last space */
                    - PATINDEX('% [^A-Z]%', col)+1 /* subtract the first throwaway chars also. */
       ) AS ss
FROM t1

Results:

|                 ss |
|--------------------|
|     100 MG PL/2 ML |
|  99.9 MG PL/2.5 ML |
|       10 mg pl/2 l |

What this won't account for though is if your final set of characters has a space in it. But if that's an issue, the final CHARINDEX() can be adjusted.

Upvotes: 1

Serkan Ekşioğlu
Serkan Ekşioğlu

Reputation: 251

you can try this one as well

declare @str varchar(50)
set @str='ABC XYZ 100 MG PL/2 ML ABC D'

select @str
select PATINDEX('%[1-9]%',@str),charindex(' ML ',@str),len(@str)
select substring(@str,PATINDEX('%[1-9]%',@str),charindex(' ML ',@str)-PATINDEX('%[1-9]%',@str)+3)

Upvotes: 1

precose
precose

Reputation: 141

 SELECT SUBSTRING(LEFT('ABC XYZ 100 MG PL/2 ML ABCD',CHARINDEX('ML', 'ABC XYZ 100 MG PL/2 ML ABCD') + 2),PATINDEX('%[0-9]%','ABC XYZ 100 MG PL/2 ML ABCD'),LEN('ABC XYZ 100 MG PL/2 ML ABCD'))

-

SELECT SUBSTRING(LEFT(col,CHARINDEX('ML', col) + 2),PATINDEX('%[0-9]%',col),LEN(col))
                                     from table

Although, you state in your question you want everything from "before" 'ML' and your expected output has 'ML' in it

Upvotes: 3

Related Questions