Jorge Torres Jorge Torres - 6 months ago 20
SQL Question

Get numbers on varchar in sql with Regex?

I have a sql table in which a column stores strings like:

CINTA NYLON CT708 DE 120MM X 1000MTRS
CINTA NYLON 102MM X 1000MTRS


I want to extract the numbers before MM and MTRS, respectively.
My result would be:

120MM, 1000MTRS
102MM, 1000MTRS


Is this possible? Beforehand thank you very much.

Answer

Not using Regex, and probably very expensive in a large dataset, but it works given the example strings in your question.

DECLARE @strings TABLE (string VARCHAR(100));

INSERT  INTO @strings
VALUES  ('CINTA NYLON CT708 DE 120MM X 1000MTRS'),
        ('CINTA NYLON 102MM X 1000MTRS');

SELECT  string,
        REVERSE(LEFT(REVERSE(SUBSTRING(string, 0, CHARINDEX('MM', string))), CHARINDEX(' ', REVERSE(SUBSTRING(string, 0, CHARINDEX('MM', string)))))) AS mm,
        REVERSE(LEFT(REVERSE(SUBSTRING(string, 0, CHARINDEX('MTRS', string))), CHARINDEX(' ', REVERSE(SUBSTRING(string, 0, CHARINDEX('MTRS', string)))))) AS mtrs
FROM    @strings;
Comments