topic Re: How to escape the ' delimiter to add it to a REGEX pattern in SQL in Data Quality

How to escape the ' delimiter to add it to a REGEX pattern in SQL

Anonymous — Sat, 16 Nov 2024 10:42:41 GMT

I added a REGEX pattern to Talend to check for valid alpha only names, I got the pattern correct except I need to add the ' character as a valid name could be O'Brian. The only issue is that that character is used to enclose the pattern in the SQL statement and if I add it causes a syntax error. Would appreciate it if someone could tell me how to include the character to the below. Thank you in advance!
[size=2][font=Verdana, sans-serif]SELECT "LASTNAME" FROM "CAIDAN"."MEMBERMASTER" WHERE ( NOT REGEXP_LIKE("LASTNAME" , '^[a-zA-Z .-]+$' ) OR "LASTNAME" IS NULL )[/font][/size]

Re: How to escape the ' delimiter to add it to a REGEX pattern in SQL

Sebastiao_Qlik — Mon, 04 Apr 2016 09:27:17 GMT

Hi Keyth,
did you try to add \ before the ' character?

Re: How to escape the ' delimiter to add it to a REGEX pattern in SQL

Anonymous — Mon, 04 Apr 2016 16:55:24 GMT

Yes, tried that and it seemed to cause a syntax error. I also noticed that a name like O'Brian in the test window also caused an error? It someone at Talend could take the REGEX posted and try it in Talend with a test name like O'Brian you should get the same error.