SQL Server RegEx

I need to match a string containing at least 6 digits. I am having an issue with this.

This example will help:

IF OBJECT_iD('tempdb..#Sample') IS NOT NULL DROP TABLE #Sample
CREATE TABLE #Sample(Field VARCHAR(50))
GO

INSERT INTO #Sample (Field) VALUES ('ABC123 ')
INSERT INTO #Sample (Field) VALUES ('ABC1')
INSERT INTO #Sample (Field) VALUES ('ABC1')
INSERT INTO #Sample (Field) VALUES ('ABC123.')
INSERT INTO #Sample (Field) VALUES ('ABC123&')
INSERT INTO #Sample (Field) VALUES ('ABC123&')
INSERT INTO #Sample (Field) VALUES ('ABC1234567&')
INSERT INTO #Sample (Field) VALUES ('ABC12345&')
INSERT INTO #Sample (Field) VALUES ('DataMigrationLog_Bak3')

DECLARE @PAT VARCHAR(500) 

SELECT '%[^a-z0-9 .]%' PAT, * FROM #Sample WHERE Field LIKE '%[^a-z0-9 .]%'

SELECT @PAT = '%[(\d{6})]%'
SELECT @PAT PATVAR, * FROM #Sample WHERE Field LIKE @PAT

SELECT '%[(\d{3})]%' PAT, * FROM #Sample WHERE Field LIKE '%[(\d{3})]%'

When using this pattern - %[(\d{6})]% It gives two rows

ABC1234567&
DataMigrationLog_Bak3

What I am missing here?

Note this question has been flagged as duplicate. While I have used the solution given in the duplicate as the query in my question - both the questions are substantially different, in my opinion.

Please check the following solution based on TRANSLATE() function.

Just SQL Server 2025 will start supporting real RegEx.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (col VARCHAR(100));
INSERT @tbl (col) VALUES
('ABC123 '),
('ABC1'),
('ABC1'),
('ABC123.'),
('ABC123&'),
('ABC123&'),
('ABC1234567&'),
('ABC12345&'),
('DataMigrationLog_Bak3');
-- DDL and sample data population, end

-- SQL Server 2017 onwards
;WITH cte AS 
(
   SELECT col
      , REPLACE(TRANSLATE(col, '0123456789', SPACE(10)),' ','') AS JunkCharacters
   FROM @tbl
)
SELECT col, JunkCharacters
   , REPLACE(TRANSLATE(col, TRIM(JunkCharacters), SPACE(LEN(TRIM(JunkCharacters)))),' ','') AS CleansedDigits
   , DATALENGTH(REPLACE(TRANSLATE(col, TRIM(JunkCharacters), SPACE(LEN(TRIM(JunkCharacters)))),' ','')) AS Digit_Counter
FROM cte;

Output