Using the debugger I saw that the expected strings were handed over to the function. If you use '\\3' at the SQL level, the C string that the C function gets is "\\3" (in C syntax). On 08/10/15 12:24, Stefan Manegold wrote:

... or maybe just their rendering in the TRACE is wrong ... ?

----- On Oct 8, 2015, at 12:22 PM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:

...

Hi,

mainly for the developer(s) who might look into this:

even if we (i.e., our pcre.replace() function supports the back references, at least the backslash passing from the query string to the MAL call is not up to the task; single backslashes disappear, double backslashes remain double; cf.,

sql>trace select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\3-\2-\1', 'i'); +---------------------------+ | pcre_replace_single_value | +===========================+ | 3-2-1 | +---------------------------+ 1 tuple (2.474ms) +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

...

| | +=======+=====================================================================================================================================================================================================+

| 0 | X_13=0@0:void := querylog.define("select

...

| \\'^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$\\', | \\'{3}-{2}-{1}\\', \\'i\\');":str,"default_pipe":str,5:int); | | 37 | X_6="3-2-1":str := | pcre.replace(A0="15/10/2015":str,A1="^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,A2="3-2-1":str,A3="i":str);

| |

...

| 9 | | sql.resultSet(".L":str,"pcre_replace_single_value":str,"clob":str,0:int,0:int,4:int,X_6="3-2-1":str);

| |

...

| 1 | end user.s4_1; | | | 527 | function user.s4_1{autoCommit=true}(A0:str,A1:str,A2:str,A3:str):void; | | | 596 | X_4=0@0:void := | user.s4_1("15/10/2015":str,"^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,"3-2-1":str,"i":str);

| |

...

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

6 tuples (2.578ms)

...

sql>trace select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\\3-\\2-\\1', 'i'); +---------------------------+ | pcre_replace_single_value | +===========================+ | \3-\2-\1 | +---------------------------+ 1 tuple (59.813ms) +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| ticks | statement

...

| | +=======+=====================================================================================================================================================================================================+

| 2 | X_13=0@0:void := querylog.define("select

| ticks | statement pcre_replace(\\'15/10/2015\\', pcre_replace(\\'15/10/2015\\',

...

| \\'^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$\\', | \\'{3}-{2}-{1}\\', \\'i\\');":str,"default_pipe":str,5:int); | | 38 | X_6="\\3-\\2-\\1":str := | pcre.replace(A0="15/10/2015":str,A1="^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,A2="\\3-\\2-\\1":str,A3="i":str);

| |

...

| 10 | | sql.resultSet(".L":str,"pcre_replace_single_value":str,"clob":str,0:int,0:int,4:int,X_6="\\3-\\2-\\1":str);

| |

...

| 1 | end user.s4_1; | | | 592 | function user.s4_1{autoCommit=true}(A0:str,A1:str,A2:str,A3:str):void; | | | 670 | X_4=0@0:void := | user.s4_1("15/10/2015":str,"^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,"\\3-\\2-\\1":str,"i":str);

| |

...

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

6 tuples (59.928ms)

...

Best, Stefan

----- On Oct 8, 2015, at 12:00 PM, imad hajj chahine imad.hajj.chahine@gmail.com wrote:

...

Hi,

Monetdb is using pcre library for regular expression support, and using this function we can allow SQL to use the regex library:

CREATE FUNCTION "pcre_replace" (origin string, pat string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace";

I want to use regex for date conversion to the format supported by monetdb (yyyy-MM-dd), I have the following format dd/MM/yyyy, and i am trying to run the following with no success:

select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\\3-\\2-\\1', 'i')

select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '{3}-{2}-{1}', 'i')

select pcre_replace('15/10/2015', '^(?<day>3[01]|[12][0-9]|0?[1-9])/(?<month>1[012]|0?[1-9])/(?<year>[0-9]{4})$',

'\\k<year>-\\k<month>-\\k<day>', 'i')

...

...

My question is, is backreference supported in the replacement string; if yes, whats is the right syntax to use to reference the detected groups, if no, is there a clever way then using substring?

Thanks

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Sjoerd Mullender

On 08/10/15 14:51, imad hajj chahine wrote:

Thank You Sjoerd,

I didnt get what do u mean by the "search which will return the offsets of the groups to the application." I tried to look for match_all function but its not in the pcre.mal module. Can you tell me which function should i use to get the position of the groups matched.

I was referring to the PCRE library, not to what MonetDB makes available from that library.

On Thu, Oct 8, 2015 at 2:20 PM, Sjoerd Mullender mailto:sjoerd@acm.org> wrote:

Using the debugger I saw that the expected strings were handed over to the function. If you use '\\3' at the SQL level, the C string that the C function gets is "\\3" (in C syntax).

On 08/10/15 12:24, Stefan Manegold wrote:

...

... or maybe just their rendering in the TRACE is wrong ... ?

----- On Oct 8, 2015, at 12:22 PM, Stefan Manegold Stefan.Manegold@cwi.nl mailto:Stefan.Manegold@cwi.nl wrote:

...

Hi,

mainly for the developer(s) who might look into this:

even if we (i.e., our pcre.replace() function supports the back references, at least the backslash passing from the query string to the MAL call is not up to the task; single backslashes disappear, double backslashes remain double; cf.,

sql>trace select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\3-\2-\1', 'i'); +---------------------------+ | pcre_replace_single_value | +===========================+ | 3-2-1 | +---------------------------+ 1 tuple (2.474ms)

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

...

...

| ticks | statement

...

...

| |

+=======+=====================================================================================================================================================================================================+

...

...

...

...

| \\'^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$\\', | \\'{3}-{2}-{1}\\', \\'i\\');":str,"default_pipe":str,5:int); | | 37 | X_6="3-2-1":str := |

| 0 | X_13=0@0:void := querylog.define("select pcre_replace(\\'15/10/2015\\', pcre.replace(A0="15/10/2015":str,A1="^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,A2="3-2-1":str,A3="i":str);

...

...

| |

...

...

| 9 | |

sql.resultSet(".L":str,"pcre_replace_single_value":str,"clob":str,0:int,0:int,4:int,X_6="3-2-1":str);

...

...

| |

...

...

| 1 | end user.s4_1; | | | 527 | function user.s4_1{autoCommit=true}(A0:str,A1:str,A2:str,A3:str):void; | | | 596 | X_4=0@0:void := |

user.s4_1("15/10/2015":str,"^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,"3-2-1":str,"i":str);

...

...

| |

...

...

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

...

...

6 tuples (2.578ms)

...

...

sql>trace select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\\3-\\2-\\1', 'i'); +---------------------------+ | pcre_replace_single_value | +===========================+ | \3-\2-\1 | +---------------------------+ 1 tuple (59.813ms)

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

...

...

| ticks | statement

...

...

| |

+=======+=====================================================================================================================================================================================================+

...

...

...

...

| \\'^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$\\', | \\'{3}-{2}-{1}\\', \\'i\\');":str,"default_pipe":str,5:int); | | 38 | X_6="\\3-\\2-\\1":str := |

| 2 | X_13=0@0:void := querylog.define("select pcre_replace(\\'15/10/2015\\', pcre.replace(A0="15/10/2015":str,A1="^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,A2="\\3-\\2-\\1":str,A3="i":str);

...

...

| |

...

...

| 10 | |

sql.resultSet(".L":str,"pcre_replace_single_value":str,"clob":str,0:int,0:int,4:int,X_6="\\3-\\2-\\1":str);

...

...

| |

...

...

| 1 | end user.s4_1; | | | 592 | function user.s4_1{autoCommit=true}(A0:str,A1:str,A2:str,A3:str):void; | | | 670 | X_4=0@0:void := |

user.s4_1("15/10/2015":str,"^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$":str,"\\3-\\2-\\1":str,"i":str);

...

...

| |

...

...

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

...

...

6 tuples (59.928ms)

...

...

Best, Stefan

----- On Oct 8, 2015, at 12:00 PM, imad hajj chahine imad.hajj.chahine@gmail.com mailto:imad.hajj.chahine@gmail.com

wrote:

...

...

Hi,

Monetdb is using pcre library for regular expression support, and using this function we can allow SQL to use the regex library:

CREATE FUNCTION "pcre_replace" (origin string, pat string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace";

I want to use regex for date conversion to the format supported by monetdb (yyyy-MM-dd), I have the following format dd/MM/yyyy, and i am trying to run the following with no success:

select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '\\3-\\2-\\1', 'i')

select pcre_replace('15/10/2015', '^(3[01]|[12][0-9]|0?[1-9])/(1[012]|0?[1-9])/([0-9]{4})$', '{3}-{2}-{1}', 'i')

select pcre_replace('15/10/2015',

'^(?<day>3[01]|[12][0-9]|0?[1-9])/(?<month>1[012]|0?[1-9])/(?<year>[0-9]{4})$',

...

...

...

'\\k<year>-\\k<month>-\\k<day>', 'i')

...

...

My question is, is backreference supported in the replacement string; if yes, whats is the right syntax to use to reference the detected groups, if no, is there a clever way then using substring?

Thanks

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ http://www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Sjoerd Mullender

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Sjoerd Mullender