On 5-12-2018 18:00, Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] wrote: > Is this sql standard concept that i must do this in this crap way?
Yes, it is specified in SQL:2016, section 6.32 <string value function>", "<regular expression substring function>". > SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+' > escape '#') FROM RDB$DATABASE > > Also strange that i must consume whole string by reg expression not only > part of it. The syntax defines which part you want to obtain in terms of its position in the rest of the string. > This can be as an option but as default it is strange for me. > > Why not simply do: > > SELECT substring('ab11c12bcd' similar '[0-9]+' itemNumber 1) FROM > RDB$DATABASE > > Will simply return „11” > > SELECT substring('ab11c22bcd' similar '[0-9]+' itemNumber 2) FROM > RDB$DATABASE > > Will simply return 22 "Why not simply" because that is not the behavior defined by the standard for this specific function. The SQL:2016 standard also has "<regex substring function>" (SUBSTRING_REGEX) and a number of related functions like LIKE_REGEX, OCCURRENCES_REGEX, TRANSLATE_REGEX and POSITION_REGEX, which use the XQuery fn:matches() regex syntax. This function allows you to specify the occurrence and capturing group to return (and some more things like start position in string), but Firebird doesn't provide this yet. > Now i have 2 udf like this: > > REG_MATCH > > REG_MATCH_COUNT > > and i supposed that i can replace it with built in one, but i see that > this is really terrible. > > Above udfs i can use in this way > > SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 1) FROM RDB$DATABASE > > Return 11 > > SELECT REG_MATCH(‘ab11c22bcd’, ‘[0-9]+’, 2) FROM RDB$DATABASE > > Return 22 > > SELECT REG_MATCH_COUNT(‘ab11c22bcd’, ‘[0-9]+’) FROM RDB$DATABASE > > Return 2 > > I can use it in the where clause: > > SELECT * FROM MY_TABLE T WHERE REG_MATCH_COUNT(T.FIELD, ‘[0-9]+’)>2 > > Or > > SELECT * FROM MY_TABLE T WHERE REG_MATCH(T.FIELD, ‘[0-9]+’, 1)=’11’ If I understand the SQL:2016 SUBSTRING_REGEX correctly, the equivalent for that would be SUBSTRING_REGEX('[0-9]+' IN T.FIELD) or - explicitly specifying the occurrence - SUBSTRING_REGEX('[0-9]+' IN T.FIELD OCCURRENCE 1). The equivalent of that REG_MATCH_COUNT would be OCCURRENCES_REGEX('[0-9]+' IN T.FIELD) Unfortunately we don't have that yet in Firebird. Mark -- Mark Rotteveel