1

I'm trying to execute simple SELECT statements in dedicated SQL Pool's script in Azure Synapse. One of those were:

SELECT regexMatch('abctest','^[A-Za-z]*$');

This statement should yield return True because it tests whether the string contains only alphabets or not. Instead it produces the following error message: 'regexMatch' is not a recognised built-in function name.'

When I checked the official docs, the built-in function is available and the task of the function was as expected. https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions

I also found this page, where it is explained to create a pipeline/data flow to execute regex these commands. https://learn.microsoft.com/en-us/azure/data-factory/tutorial-data-flow

I tried it and was successful with it, but I need the regexMatch and regexReplace functions to work in simple SQL scripts and SELECT commands, it seems much simpler and less tedious. Is there any way I can do it? Or is using data flow the only possible way?

Thanks in advance.

AAA6
  • 67
  • 3
  • 9

1 Answers1

0

Azure Data flow expressions are different from SQL query functions. They both are not same.

Currently regexMatch and regexReplace are not built in functions and not supported in Synapse SQL. You can see I got the same error for regexReplace.

enter image description here

Also, I checked the same in SQL database and it gave the same error which means those are not supported in SQL as well.

Coming to the Dataflow expressions, they are more related to spark and not same as SQL functions.

You can build the equivalent SQL query for SELECT regexMatch('abctest','^[A-Za-z]*$'); using LIKE and NOT LIKE.

SELECT CASE WHEN 'abctest' NOT LIKE '%[^A-Za-z]%' THEN 0 ELSE 1 END AS res;

enter image description here

And for regexReplace, you need to use the combinations of string functions of SQL as per your requirement.

Go through some workarounds given in this SO Answer by @Mukus using replace,LIKE, PATINDEX, LEFT and RIGHT.

If you are using dataflows, its better use the functions in dataflow to achieve your requirement as it might not be possible to replicate all the regular expressions in SQL query.

Rakesh Govindula
  • 5,257
  • 1
  • 2
  • 11
  • Thanks for the reply, will check the regexReplace answer out. LIKE doesn't replicate the exact functionality of regexMatch. RLIKE does and it is also not available in Synapse. LIKE doesn't support regular expression. – AAA6 Jul 14 '23 at 10:39
  • Not only in synapse, RLIKE is not supported in SQL queries also. If you are using dataflows then its better to use those functions in dataflows as REGEXP are not supported in SQL. If you want to queries, then you need to change the pattern according to your regular expression. – Rakesh Govindula Jul 14 '23 at 12:04