-1

I'm working in Matillion/Snowflake and stuck on some REGEXP functions. I have a varchar field with a few entries containing letters and hyphens and looking for a way to make any field contains letters or hyphens be 0.

Any help would be welcome! Been stuck on this for awhile.

Thank you!

Here is what I have so far:

select SHIP_TO_CUSTOMER_NUMBER,
CASE
WHEN REGEXP_LIKE(SHIP_TO_CUSTOMER_NUMBER, '[a-zA-Z]*') THEN '0'
ELSE SHIP_TO_CUSTOMER_NUMBER
END as test_field
from sales_fact_m3;

So far it turns any field that has just letters such as 'XRIA' to 0 but there is a field 'HWL-WL1' that still comes through as 'HWL-WL1'.

I thought that the above REGEXP_LIKE statement would turn any field with any letter in it to 0 but I guess it's not working like that for some reason.

Pray4Tre
  • 5
  • 1

1 Answers1

-1

Use .[a-zA-Z]. instead

-as mentioned by markalex

Pray4Tre
  • 5
  • 1