Presto / MySQL - Regex to check column values are alphanumeric or not

Question

I am looking to get only alphanumeric values in the column. I have tried following Presto query but I am still getting numeric and alphanumeric values.

Query:

select 
   seller_id
from 
   myTable 
where 
  logdate = '2019-10-07'
  and regexp_like(seller_id,'^[a-z0-9A-Z]+$')

Actual Result:

12345
f7c865ff
1003147
c743a319
z87wm
google

Expected Result:

f7c865ff
c743a319
z87wm
google

Can anyone help how I can improve this so I can only get alphanumeric values?

CAST should work in presto also, read this: https://stackoverflow.com/a/38143497/2700344 — leftjoin, Oct 08 '19 at 12:09

score 4 · Accepted Answer · answered Oct 08 '19 at 02:05

4

try this one please

select 
   seller_id
from 
   myTable 
where 
  logdate = '2019-10-07'
  and 
  regexp_like(seller_id,'^[a-zA-Z]+[a-z0-9A-Z]+$')

This excludes everything that has not at the beginning a letter( upper or lower case)

answered Oct 08 '19 at 02:05

nbk

45,398
8
30
47

Presto / MySQL - Regex to check column values are alphanumeric or not

1 Answers1