Start:

* Data Manipulation Language (DML) [#vceff53f] cf) [[Firebird 2.5 Release Notes:http://www.firebirdsql.o... ** RegEx Search Support using SIMILAR TO [#q4f95232] A new SIMILAR TO predicate is introduced to support regul... Syntax Patterns <similar predicate> ::= <value> [ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <e... <similar pattern> ::= <character value expression: regula... <regular expression> ::= <regular term> | <regular expression> <vertical bar> <regular term> <regular term> ::= <regular factor> | <regular term> <regular factor> <regular factor> ::= <regular primary> | <regular primary> <asterisk> | <regular primary> <plus sign> | <regular primary> <question mark> | <regular primary> <repeat factor> <repeat factor> ::= <left brace> <low value> [ <upper limit> ] <right brace> <upper limit> ::= <comma> [ <high value> ] <low value> ::= <unsigned integer> <high value> ::= <unsigned integer> <regular primary> ::= <character specifier> | <percent> | <regular character set> | <left paren> <regular expression> <right paren> <character specifier> ::= <non-escaped character> | <escaped character> <regular character set> ::= <underscore> | <left bracket> <character enumeration>... <right br... | <left bracket> <circumflex> <character enumeration>... | <left bracket> <character enumeration include>... <... <right bracket> <character enumeration include> ::= <character enumeration> <character enumeration exclude> ::= <character enumeration> <character enumeration> ::= <character specifier> | <character specifier> <minus sign> <character speci... | <left bracket> <colon> <character class identifier>... <character specifier> ::= <non-escaped character> | <escaped character> <character class identifier> ::= ALPHA | UPPER | LOWER | DIGIT | SPACE | WHITESPACE | ALNUM Note <non-escaped character> is any character except <left bra... <escaped character> is the <escape character> succeeded b... Table 10.1. Character class identifiers Identifier Description Note ALPHA All characters that are simple latin letters (a-z, ... UPPER All characters that are simple latin uppercase lett... LOWER All characters that are simple latin lowercase lett... DIGIT All characters that are numeric digits (0-9) SPACE All characters that are the space character (ASCII 32) WHITESPACE All characters that are whitespaces (vertical ... ALNUM All characters that are simple latin letters (ALPHA... Usage Guide Return true for a string that matches <regular expression... <regular expression> <vertical bar> <regular term> 'ab' SIMILAR TO 'ab|cd|efg' -- true 'efg' SIMILAR TO 'ab|cd|efg' -- true 'a' SIMILAR TO 'ab|cd|efg' -- false Match zero or more occurrences of <regular primary>: <reg... '' SIMILAR TO 'a*' -- true 'a' SIMILAR TO 'a*' -- true 'aaa' SIMILAR TO 'a*' -- true Match one or more occurrences of <regular primary>: <regu... '' SIMILAR TO 'a+' -- false 'a' SIMILAR TO 'a+' -- true 'aaa' SIMILAR TO 'a+' -- true Match zero or one occurrence of <regular primary>: <regul... '' SIMILAR TO 'a?' -- true 'a' SIMILAR TO 'a?' -- true 'aaa' SIMILAR TO 'a?' -- false Match exact <low value> occurrences of <regular primary>:... '' SIMILAR TO 'a{2}' -- false 'a' SIMILAR TO 'a{2}' -- false 'aa' SIMILAR TO 'a{2}' -- true 'aaa' SIMILAR TO 'a{2}' -- false Match <low value> or more occurrences of <regular primary... '' SIMILAR TO 'a{2,}' -- false 'a' SIMILAR TO 'a{2,}' -- false 'aa' SIMILAR TO 'a{2,}' -- true 'aaa' SIMILAR TO 'a{2,}' -- true Match <low value> to <high value> occurrences of <regular... '' SIMILAR TO 'a{2,4}' -- false 'a' SIMILAR TO 'a{2,4}' -- false 'aa' SIMILAR TO 'a{2,4}' -- true 'aaa' SIMILAR TO 'a{2,4}' -- true 'aaaa' SIMILAR TO 'a{2,4}' -- true 'aaaaa' SIMILAR TO 'a{2,4}' -- false Match any (non-empty) character: <underscore> '' SIMILAR TO '_' -- false 'a' SIMILAR TO '_' -- true '1' SIMILAR TO '_' -- true 'a1' SIMILAR TO '_' -- false Match a string of any length (including empty strings): <... '' SIMILAR TO '%' -- true 'az' SIMILAR TO 'a%z' -- true 'a123z' SIMILAR TO 'a%z' -- true 'azx' SIMILAR TO 'a%z' -- false Group a complete <regular expression> to use as one singl... 'ab' SIMILAR TO '(ab){2}' -- false 'aabb' SIMILAR TO '(ab){2}' -- false 'abab' SIMILAR TO '(ab){2}' -- true Match a character identical to one of <character enumerat... 'b' SIMILAR TO '[abc]' -- true 'd' SIMILAR TO '[abc]' -- false '9' SIMILAR TO '[0-9]' -- true '9' SIMILAR TO '[0-8]' -- false Match a character not identical to one of <character enum... 'b' SIMILAR TO '[^abc]' -- false 'd' SIMILAR TO '[^abc]' -- true Match a character identical to one of <character enumerat... '3' SIMILAR TO '[[:DIGIT:]^3]' -- false '4' SIMILAR TO '[[:DIGIT:]^3]' -- true Match a character identical to one character included in ... '4' SIMILAR TO '[[:DIGIT:]]' -- true 'a' SIMILAR TO '[[:DIGIT:]]' -- false '4' SIMILAR TO '[^[:DIGIT:]]' -- false 'a' SIMILAR TO '[^[:DIGIT:]]' -- true Examples create table department ( number numeric(3) not null, name varchar(25) not null, phone varchar(14) check (phone similar to '\([0-9]{3}\) [0-9]{3}\-[0-9]{4... ); insert into department values ('000', 'Corporate Headquarters', '(408) 555-123... insert into department values ('100', 'Sales and Marketing', '(415) 555-1234'); insert into department values ('140', 'Field Office: Canada', '(416) 677-1000'); insert into department values ('600', 'Engineering', '(408) 555-123'); -- chec... select * from department where phone not similar to '\([0-9]{3}\) 555\-%' escape... ** Hex Literal Support [#b4197b32] Support for hexadecimal numeric and binary string literal... Syntax Patterns <numeric hex literal> ::= { 0x | 0X } <hexit> [ <hexit>... ] <binary string literal> ::= { x | X } <quote> [ { <hexit> <hexit> }... ] <quote> <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 <hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f Numeric Hex Literals The number of <hexit> in the string cannot exceed 16. If the number of <hexit> is greater than eight, the const... Tip That means 0xF0000000 is -268435456 and 0x0F0000000 is 40... Binary String Literals The resulting string is defined as a CHAR(n/2) CHARACTER ... Examples select 0x10, cast('0x0F0000000' as bigint) from rdb$database; select x'deadbeef' from rdb$database; ** New UUID Conversion Functions [#kd4630f5] Adriano dos Santos Fernandes Tracker references CORE-1656 and CORE-1682. Two new built-in functions, UUID_TO_CHAR and CHAR_TO_UUID... CHAR_TO_UUID() The function CHAR_TO_UUID() converts the CHAR(32) ASCII r... Syntax Model CHAR_TO_UUID( <string> ) Example select char_to_uuid('93519227-8D50-4E47-81AA-8F6678C096A1') from rdb$database; UUID_TO_CHAR() The function UUID_TO_CHAR() converts a CHAR(16) OCTETS UU... Syntax Model UUID_TO_CHAR( <string> ) Example select uuid_to_char(gen_uuid()) from rdb$database; ** SOME_COL = ? OR ? IS NULL Predication [#sfd26c3f] By popular request, particularly from Delphi programmers,... Users of Delphi and other programming interfaces that app... WHERE col1 = :param1 OR :param1 IS NULL At the API level, the language interface translates the q... WHERE col1 = ? OR ? IS NULL That presented two problems: While the programmer treated the parameter :param1 as tho... The second parameter is of an unknown data type and the p... What was needed to solve this problem was to introduce a ... The implementation works like this. To resolve the first ... WHERE col1 = :param1 OR :param2 IS NULL If “param1” is not NULL, the language interface is requir... If “param2” is NULL, the language interface is required t... In other words, for the parameter ( ? ) in ? IS NULL : XSQLVAR.sqlind should be set in accordance with NULL/NON-... The XSQLVAR.sqldata of a SQL_NULL type of parameter shoul... NULL Specified in the Output Set When NULL is specified as an output constant (select NULL... ** Extension to LIST() Function [#zeb27ffc] A string expression is now allowed as the delimiter argum... Example SELECT DISCUSSION_ID, LIST(COMMMENT, ASCII_CHAR(13)) FROM COMMENTS GROUP BY DISCUSSION_ID; ** Extension to DATEADD and DATEDIFF() Functions [#m442612d] The WEEK unit was introduced for functions DATEADD and DA... MILLISECOND, SECOND, MINUTE and HOUR units are no longer ... ** BIN_NOT() Function Added [#x8bf09d2] Completing the set of built-in binary functions added in ... Syntax Pattern BIN_NOT( <number> ) Example select bin_not(flags) from x; ** Write to Temporary Tables in a Read-Only Database [#p7... (V.2.5.1) Write operations to global temporary tables in ... Optimizer Improvements Changes in optimizer logic that address recognised proble... CROSS JOIN Logic (D. Yemanov) When a CROSS JOIN involved an empty table, the optimizer ... Tracker reference CORE-2200. Note The same change was implemented in V.2.1.2. Derived Tables (A. dos Santos Fernandes) The limit on the number of contexts available when using ... Tracker reference CORE-2029. Timing of DEFAULT Evaluation (A. dos Santos Fernandes) Under rare conditions, the early evaluation of a DEFAULT ... Tracker reference CORE-1842. Index Use for NOT IN Searches (A. dos Santos Fernandes) Better performance has been achieved for the NOT IN predi... Tracker reference CORE-1137. Undo Log Memory Consumption (D. Yemanov) Excessive memory consumption by the Undo log after a leng... Tracker reference CORE-1477. ** Other Improvements [#d6848a9e] Other changes to smooth out the little annoyances include: FREE_IT Error Detection (A. dos Santos Fernandes) Previously, a UDF declared with FREE_IT would crash if th... Tracker reference CORE-1937. “Expression evaluation not supported” message improved (C... A number of secondary GDS codes were introduced to provid... 'Argument for @1 in dialect 1 must be string or numeric' 'Strings cannot be added to or subtracted from DATE or ... 'Invalid data type for subtraction involving DATE, TIME... etc. These detailed messages follow the GDS code for the isc_e... Tracker reference CORE-1799.

End:

* Data Manipulation Language (DML) [#vceff53f] cf) [[Firebird 2.5 Release Notes:http://www.firebirdsql.o... ** RegEx Search Support using SIMILAR TO [#q4f95232] A new SIMILAR TO predicate is introduced to support regul... Syntax Patterns <similar predicate> ::= <value> [ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <e... <similar pattern> ::= <character value expression: regula... <regular expression> ::= <regular term> | <regular expression> <vertical bar> <regular term> <regular term> ::= <regular factor> | <regular term> <regular factor> <regular factor> ::= <regular primary> | <regular primary> <asterisk> | <regular primary> <plus sign> | <regular primary> <question mark> | <regular primary> <repeat factor> <repeat factor> ::= <left brace> <low value> [ <upper limit> ] <right brace> <upper limit> ::= <comma> [ <high value> ] <low value> ::= <unsigned integer> <high value> ::= <unsigned integer> <regular primary> ::= <character specifier> | <percent> | <regular character set> | <left paren> <regular expression> <right paren> <character specifier> ::= <non-escaped character> | <escaped character> <regular character set> ::= <underscore> | <left bracket> <character enumeration>... <right br... | <left bracket> <circumflex> <character enumeration>... | <left bracket> <character enumeration include>... <... <right bracket> <character enumeration include> ::= <character enumeration> <character enumeration exclude> ::= <character enumeration> <character enumeration> ::= <character specifier> | <character specifier> <minus sign> <character speci... | <left bracket> <colon> <character class identifier>... <character specifier> ::= <non-escaped character> | <escaped character> <character class identifier> ::= ALPHA | UPPER | LOWER | DIGIT | SPACE | WHITESPACE | ALNUM Note <non-escaped character> is any character except <left bra... <escaped character> is the <escape character> succeeded b... Table 10.1. Character class identifiers Identifier Description Note ALPHA All characters that are simple latin letters (a-z, ... UPPER All characters that are simple latin uppercase lett... LOWER All characters that are simple latin lowercase lett... DIGIT All characters that are numeric digits (0-9) SPACE All characters that are the space character (ASCII 32) WHITESPACE All characters that are whitespaces (vertical ... ALNUM All characters that are simple latin letters (ALPHA... Usage Guide Return true for a string that matches <regular expression... <regular expression> <vertical bar> <regular term> 'ab' SIMILAR TO 'ab|cd|efg' -- true 'efg' SIMILAR TO 'ab|cd|efg' -- true 'a' SIMILAR TO 'ab|cd|efg' -- false Match zero or more occurrences of <regular primary>: <reg... '' SIMILAR TO 'a*' -- true 'a' SIMILAR TO 'a*' -- true 'aaa' SIMILAR TO 'a*' -- true Match one or more occurrences of <regular primary>: <regu... '' SIMILAR TO 'a+' -- false 'a' SIMILAR TO 'a+' -- true 'aaa' SIMILAR TO 'a+' -- true Match zero or one occurrence of <regular primary>: <regul... '' SIMILAR TO 'a?' -- true 'a' SIMILAR TO 'a?' -- true 'aaa' SIMILAR TO 'a?' -- false Match exact <low value> occurrences of <regular primary>:... '' SIMILAR TO 'a{2}' -- false 'a' SIMILAR TO 'a{2}' -- false 'aa' SIMILAR TO 'a{2}' -- true 'aaa' SIMILAR TO 'a{2}' -- false Match <low value> or more occurrences of <regular primary... '' SIMILAR TO 'a{2,}' -- false 'a' SIMILAR TO 'a{2,}' -- false 'aa' SIMILAR TO 'a{2,}' -- true 'aaa' SIMILAR TO 'a{2,}' -- true Match <low value> to <high value> occurrences of <regular... '' SIMILAR TO 'a{2,4}' -- false 'a' SIMILAR TO 'a{2,4}' -- false 'aa' SIMILAR TO 'a{2,4}' -- true 'aaa' SIMILAR TO 'a{2,4}' -- true 'aaaa' SIMILAR TO 'a{2,4}' -- true 'aaaaa' SIMILAR TO 'a{2,4}' -- false Match any (non-empty) character: <underscore> '' SIMILAR TO '_' -- false 'a' SIMILAR TO '_' -- true '1' SIMILAR TO '_' -- true 'a1' SIMILAR TO '_' -- false Match a string of any length (including empty strings): <... '' SIMILAR TO '%' -- true 'az' SIMILAR TO 'a%z' -- true 'a123z' SIMILAR TO 'a%z' -- true 'azx' SIMILAR TO 'a%z' -- false Group a complete <regular expression> to use as one singl... 'ab' SIMILAR TO '(ab){2}' -- false 'aabb' SIMILAR TO '(ab){2}' -- false 'abab' SIMILAR TO '(ab){2}' -- true Match a character identical to one of <character enumerat... 'b' SIMILAR TO '[abc]' -- true 'd' SIMILAR TO '[abc]' -- false '9' SIMILAR TO '[0-9]' -- true '9' SIMILAR TO '[0-8]' -- false Match a character not identical to one of <character enum... 'b' SIMILAR TO '[^abc]' -- false 'd' SIMILAR TO '[^abc]' -- true Match a character identical to one of <character enumerat... '3' SIMILAR TO '[[:DIGIT:]^3]' -- false '4' SIMILAR TO '[[:DIGIT:]^3]' -- true Match a character identical to one character included in ... '4' SIMILAR TO '[[:DIGIT:]]' -- true 'a' SIMILAR TO '[[:DIGIT:]]' -- false '4' SIMILAR TO '[^[:DIGIT:]]' -- false 'a' SIMILAR TO '[^[:DIGIT:]]' -- true Examples create table department ( number numeric(3) not null, name varchar(25) not null, phone varchar(14) check (phone similar to '\([0-9]{3}\) [0-9]{3}\-[0-9]{4... ); insert into department values ('000', 'Corporate Headquarters', '(408) 555-123... insert into department values ('100', 'Sales and Marketing', '(415) 555-1234'); insert into department values ('140', 'Field Office: Canada', '(416) 677-1000'); insert into department values ('600', 'Engineering', '(408) 555-123'); -- chec... select * from department where phone not similar to '\([0-9]{3}\) 555\-%' escape... ** Hex Literal Support [#b4197b32] Support for hexadecimal numeric and binary string literal... Syntax Patterns <numeric hex literal> ::= { 0x | 0X } <hexit> [ <hexit>... ] <binary string literal> ::= { x | X } <quote> [ { <hexit> <hexit> }... ] <quote> <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 <hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f Numeric Hex Literals The number of <hexit> in the string cannot exceed 16. If the number of <hexit> is greater than eight, the const... Tip That means 0xF0000000 is -268435456 and 0x0F0000000 is 40... Binary String Literals The resulting string is defined as a CHAR(n/2) CHARACTER ... Examples select 0x10, cast('0x0F0000000' as bigint) from rdb$database; select x'deadbeef' from rdb$database; ** New UUID Conversion Functions [#kd4630f5] Adriano dos Santos Fernandes Tracker references CORE-1656 and CORE-1682. Two new built-in functions, UUID_TO_CHAR and CHAR_TO_UUID... CHAR_TO_UUID() The function CHAR_TO_UUID() converts the CHAR(32) ASCII r... Syntax Model CHAR_TO_UUID( <string> ) Example select char_to_uuid('93519227-8D50-4E47-81AA-8F6678C096A1') from rdb$database; UUID_TO_CHAR() The function UUID_TO_CHAR() converts a CHAR(16) OCTETS UU... Syntax Model UUID_TO_CHAR( <string> ) Example select uuid_to_char(gen_uuid()) from rdb$database; ** SOME_COL = ? OR ? IS NULL Predication [#sfd26c3f] By popular request, particularly from Delphi programmers,... Users of Delphi and other programming interfaces that app... WHERE col1 = :param1 OR :param1 IS NULL At the API level, the language interface translates the q... WHERE col1 = ? OR ? IS NULL That presented two problems: While the programmer treated the parameter :param1 as tho... The second parameter is of an unknown data type and the p... What was needed to solve this problem was to introduce a ... The implementation works like this. To resolve the first ... WHERE col1 = :param1 OR :param2 IS NULL If “param1” is not NULL, the language interface is requir... If “param2” is NULL, the language interface is required t... In other words, for the parameter ( ? ) in ? IS NULL : XSQLVAR.sqlind should be set in accordance with NULL/NON-... The XSQLVAR.sqldata of a SQL_NULL type of parameter shoul... NULL Specified in the Output Set When NULL is specified as an output constant (select NULL... ** Extension to LIST() Function [#zeb27ffc] A string expression is now allowed as the delimiter argum... Example SELECT DISCUSSION_ID, LIST(COMMMENT, ASCII_CHAR(13)) FROM COMMENTS GROUP BY DISCUSSION_ID; ** Extension to DATEADD and DATEDIFF() Functions [#m442612d] The WEEK unit was introduced for functions DATEADD and DA... MILLISECOND, SECOND, MINUTE and HOUR units are no longer ... ** BIN_NOT() Function Added [#x8bf09d2] Completing the set of built-in binary functions added in ... Syntax Pattern BIN_NOT( <number> ) Example select bin_not(flags) from x; ** Write to Temporary Tables in a Read-Only Database [#p7... (V.2.5.1) Write operations to global temporary tables in ... Optimizer Improvements Changes in optimizer logic that address recognised proble... CROSS JOIN Logic (D. Yemanov) When a CROSS JOIN involved an empty table, the optimizer ... Tracker reference CORE-2200. Note The same change was implemented in V.2.1.2. Derived Tables (A. dos Santos Fernandes) The limit on the number of contexts available when using ... Tracker reference CORE-2029. Timing of DEFAULT Evaluation (A. dos Santos Fernandes) Under rare conditions, the early evaluation of a DEFAULT ... Tracker reference CORE-1842. Index Use for NOT IN Searches (A. dos Santos Fernandes) Better performance has been achieved for the NOT IN predi... Tracker reference CORE-1137. Undo Log Memory Consumption (D. Yemanov) Excessive memory consumption by the Undo log after a leng... Tracker reference CORE-1477. ** Other Improvements [#d6848a9e] Other changes to smooth out the little annoyances include: FREE_IT Error Detection (A. dos Santos Fernandes) Previously, a UDF declared with FREE_IT would crash if th... Tracker reference CORE-1937. “Expression evaluation not supported” message improved (C... A number of secondary GDS codes were introduced to provid... 'Argument for @1 in dialect 1 must be string or numeric' 'Strings cannot be added to or subtracted from DATE or ... 'Invalid data type for subtraction involving DATE, TIME... etc. These detailed messages follow the GDS code for the isc_e... Tracker reference CORE-1799.

Page: