Using SQL like with range operator in Databricks

Question

In a Databricks SQL endpoint, the SQL like with range operator is not working.

Ex1:-

with Cte_Members AS

( Select '1A' as Memid

union all

Select '2B' as Memid

union all

Select '3C' as Memid

union all

Select 'DD' as Memid

)

select * from Cte_Members where Memid like '[0-9]%'

Result:- 0 records

Ex2:- with Cte_Members AS

( Select '1A' as Memid

union all

Select '2B' as Memid

union all

Select '3C' as Memid

union all

Select 'DD' as Memid

)

select * from Cte_Members where Memid like '2%'

Result:- We are getting one record with "2B" as Memid

Answer 1

You need to use rlike instead of like for regex match ^[0-9].

with Cte_Members AS
( Select '1A' as Memid
union all
Select '2B' as Memid
union all
Select '3C' as Memid
union all
Select 'DD' as Memid
)
select * from Cte_Members where Memid rlike '^[0-9]'
#Memid
#1A
#2B
#3C