I've a table called idx with following data
id dex
-- ------------
1 Major 12354
2 CITY 541 DER
3 9987741221
4 7845741
5 789456 2121
how to select rows with only digits and it should 10 digits only
expected output is
id dex
-- -----------
3 9987741221
5 789456 2121
You can use regex to match your criteria ^[0-9]{10,10}$, and I've used replace() function to remove empty space between characters(dex in id 5)
select *
from idx
where replace(dex,' ','')~'^[0-9]{10,10}$' -- or '^[0-9]{10,}$'
^[0-9]{10,10}$ :
[0-9] - To get digits
{10,10}$ - Maximum and minimum value in selected string in your case both are 10
OR
Using char_length()
select *
from idx
where dex~'^[0-9]'
and char_length(replace(dex,' ','')) = 10