Filter strings with fixed number of digits [closed]

by

I would like to subset a table where a string variable ‘my_notes’ contains exactly 10 digits phone number in MS SQL Server. I want something like

select * 
from mytable 
where my_notes like "%[0-9]{10, 10}%"

In standard regex I can use {10, 10} to specify min and max of the number of times of match. For example {10, 10} means I want to match [0-9] exactly 10 times. Somehow this does not work in SQL. What is the best way to write this kind of query?

  • 2

    SQL Server does not support Regex, only basic pattern matching.

    – 

  • As written the % will cause it to match strings with at least 10 digits (11 digits will also match, with the 11th being in the %). Is that intentional / acceptable?

    – 




  • REPLICATE(‘[0-9]’, 10) is probably your best bet here.

    – 

You should be able to do

my_notes like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

Leave a Comment