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 | +=======+=====================================================================================================================================================================================================+ | 0 | X_13=0@0:void := querylog.define("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\\');":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 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) |