Selecting specified values

Question

This is the table

chr
chr1
chr2
chr10
chrU_X
chrM_a_a

I want to get only chr%andnumber% (chr1, chr2,chr10 this case) with:

select chr
from varianti
where chr like 'chr_'
group by chr

But it returns only chr1 , chr2 not chr10.

I want to get the result like this:

chr 
chr1
chr2
chr10

Is there somethings wrong?

Answer 1

You could use SIMILAR TO:

select *
from chr
where col similar to 'chr[0-9]*'

or a regular expression:

select *
from chr
where col ~ '^chr[0-9]*$'

please see a fiddle here.