Show a string that contains two numbers in SQL [closed]

I’m studying SQL just recently and I have encountered a problem. I want to show the Address that contains only 2 digit numbers.

Example data below:

120 Annapolis St. Greenhills, San Juan City
680 M. Malvar St. Malate, Manila
71 Mt. Mayon St., Singson Village Subangdaku, Mandaue City
98-C Padaba Street
Brgy 1 Zone 13 Aquino

The output should be:

71 Mt. Mayon St., Singson Village Subangdaku, Mandaue City
98-C Padaba Street
Brgy 1 Zone 13 Aquino

Answer

One set-based approach you could try is to split the characters of each address into rows and count the numerical values, then filter using an aggregate. A slight complication is where you have separate single digit numbers, which can be filtered out using patindex.

To split the string requires use of a numbers / tally table – I’m using a CTE just for a working example, a permanent table would be preferable and has many uses.

with t as (select top (60) Row_Number() over (order by (select null)) n from sys.sysmessages)

select Address
from Tablename cross apply t
where t.n <= Len(address) and t.n-1 !=patindex('% [0-9] %',address)
group by address
having Sum(case when Ascii(Substring(address,t.n,1)) between 48 and 57 then 1 else 0 end) = 2

See example DB Fiddle

Another possible solution could be with just patindex although more sample data would really be required.

For the sample data given the following returns the expected result:

select * 
from t
where patindex('[0-9][0-9][^0-9]%',address)>0 
  or patindex('%[^0-9][0-9][0-9][^0-9]%',address)>0