I have column with partner ID and I would like to flag in my script partners with prefix ZD and the numer on the third place (ZD0, ZD3, ZD9 but without ZDQ).
maybe something like
if(left(partner ID,3) <> 'ZDQ' and left(partner ID,2) = 'ZD', 'Yes','No') as ZDFlag
If( Left( ID, 2) = 'ZD' and isnum(mid( ID, 3,1)), 'Yes','No') as Flag
if(left([partner ID],2) = 'ZD' and match(right([partner ID],1),1,2,3,4,5,6,7,8,9,0), 'Yes','No') as flag
Just because I've always wanted to find a use for the "precedes" operator:
=TextBetween([Partner ID],'ZD','') precedes 'A'
..but watch out, help means precedes operator is using ASCII Comparison, but it's not:
= '9' precedes ':' => false
= ord('9') < ord(':') => true
So, my guessing is some other internal order is used.
hope this helps
Unfortunately, this also accepts codes with second char being any number like:
Probably because 'D' is a format code coincidentially.
'D1' also is flagged.
Maybe not the best solution after all
Retrieving data ...