How do I evaluate whether the first three characters of a string are all ALPHA in Snowflake?-CodePudding

The value of my field is 'VNF123'.

My line of code is left(field,3) regexp '^[A-Z]{3}'

It returns FALSE

I tried a literal: 'VNF' regexp '^[A-Z]{3}'

And it returns TRUE

CodePudding user response：

REGEX is an alias to RLIKE, which take parameters verse the in the middle form of REGEXP so I have gone for that:

But core thing to know is in snowflake some of the REPEXP function have implicit anchor tokens ^,$ added to them, a REPEXP/RLIKE is one of them:

thus:

select 
    column1 as field
    ,left(field,3) as l_field
    ,rlike(field, '^[A-Z]{3}') as r1
    ,rlike(l_field, '^[A-Z]{3}') as r2
    ,rlike(field, '^[A-Z]{3}.*') as r3
from values
    ('VNF123'),
    ('VNF'),
    ('NF123')
    ;

gives:

FIELD	L_FIELD	R1	R2	R3
VNF123	VNF	FALSE	TRUE	TRUE
VNF	VNF	TRUE	TRUE	TRUE
NF123	NF1	FALSE	FALSE	FALSE

which shows the third row always fails as expected, and the second always passes, but the first passes when the pattern takes this implicit anchor into account.

thus the solution is to add a .* to match the tail, and you are away laughing.

field regexp '^[A-Z]{3}.*'

CodePudding user response：

It turns out there was some kind of control character in the first position of my string. When I added left(ltrim(field),3) regexp '^[A-Z]{3}' - it evaluated TRUE as expected.