CODEWITHSUNDEEP
sqlsql-serverregex
Jorge Torres
Jorge Torres

Reputation: 70

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?

Upvotes: 0

Views: 792

Answers (4)

TheGameiswar
TheGameiswar

Reputation: 28940

One version using Numbers table:if your string has an index on it and if it is less than 80 chars,SQL can accurately guess stats on this type of like condition as well

declare @id varchar(max)
set @id=' '+'CINTA NYLON 102MM X 1000MTRS'+' '

;With cte
as
(
select 
SUBSTRING(@id,n+1,charindex(' ',@id,n+1)-n-1) as string
from
numbers n
where 
substring(@id,n,1)=' '
and n<len(@id)
)
select * from cte where string like '%mm%' or string like '%mtrs%'.

Upvotes: 0

Maciej Los
Maciej Los

Reputation: 8591

Using PATINDEX:

DECLARE @tmp TABLE(Whatever VARCHAR(255))

INSERT INTO @tmp(Whatever)
VALUES('CINTA NYLON CT708 DE 120MM X 1000MTRS'),
('CINTA NYLON 102MM X 1000MTRS')
--
SELECT SUBSTRING(Whatever,PATINDEX('%[0-9][0-9][0-9]MM%', Whatever), 5) AS Col1, 
            SUBSTRING(Whatever,PATINDEX('%[0-9][0-9][0-9][0-9]MTRS%', Whatever), 8) AS Col2
FROM @tmp

For further information, please see:
LIKE
SUBSTRING

Upvotes: 1

vercelli
vercelli

Reputation: 4767

As far as I know SQL Server 2008 R2 doesn't have RegExp implemented. We faced the same problem and fixed it using Master Data Services Functions. We now have a function like this one:

CREATE FUNCTION [RegexExtract](@input [nvarchar](4000), @pattern [nvarchar](4000), @group [nvarchar](4000), @mask [tinyint])
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS 
EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[RegexExtract]
GO

Follow this link to get more info: https://dyball.wordpress.com/2011/11/01/sql-2008-r2-regular-expressions/

Upvotes: 0

Chris Pickford
Chris Pickford

Reputation: 9001

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;

Upvotes: 3

Related Questions