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?
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;
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
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/
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%'.
"CINTA NYLON CT708 DE 120MM X 1000MTRS".match(/(\d*MM)|(\d*MTRS)/gi)