SQL Regex for 7 digit number in varchar field-CodePudding

Question: What's the best way to 'extract' all Rows where the TEXT column contains a 7 digit number starting with 7 and only show the 7 digit number?

SQL Server 2017:

CREATE TABLE [TABLE_1] (
TEXT varchar(50) Null,
);

INSERT INTO TABLE_1
(TEXT)
VALUES
('7005011'),
('The Number is 7335022'),
('asd*.-: wqe'),
('/7225033/.123'),
('Nr.: 7115044')
;

SQL Fiddle Link

The desired result would be:

 --------- 
| TEXT    |
 --------- 
| 7005011 |
| 7335022 |
| 7225033 |
| 7115044 |
 ---------

CodePudding user response：

There's no RegEx in SQL Server natively, you need to use PATINDEX, SUBSTRING, and LIKE:

SELECT [TEXT] = SUBSTRING([TEXT], 
  PATINDEX('%7'   REPLICATE('[0-9]', 6)   '%', [TEXT]), 7)
FROM dbo.TABLE_1
WHERE [TEXT] LIKE '%7'   REPLICATE('[0-9]', 6)   '%';

Output:

TEXT
7005011
7335022
7225033
7115044

Example db<>fiddle