Procedure using the SUBSTRING() function (Susbstr procedure)
<< Sample procedure: determining age | IBExpert | Trigger >>
Unfortunately Firebird 1.5 does not allow any variable parameters in the SUBSTRING() SQL function.
Although there are diverse UDF implementations, for those preferring to use stored procedures, here is an example from Lucas Franzen:
(For those of you who may be wondering what on earth "Donaudampfschiffahrtsgesellschaftskapitän" is, it is the German word for "Donau Steam Navigation Company Captain"!).
Call:
SELECT RESULT FROM SP_SUBSTRING
( INPUTSTRING, STARTPOS, NO_CHAR_FROM_STARTPOS ).
E.g.: SELECT RESULT FROM SP_SUBSTRING
( 'Donaudampfschiffahrtsgesellschaftskapitän', 1, 10 )
--> Donaudampf
E.g.: SELECT RESULT FROM SP_SUBSTRING
( 'Donaudampfschiffahrtsgesellschaftskapitän', 35, 8 )
--> kapitän
CREATE PROCEDURE SP_SUBSTRING (
SRC VARCHAR (255),
START_AT INTEGER,
NLEN INTEGER
)
RETURNS (
RESULT VARCHAR (255)
)
AS
declare variable II INTEGER;
declare variable VGL VARCHAR(255);
declare variable PFX VARCHAR(255);
declare variable C CHAR(1);
BEGIN
/* Version : 1 */
/* Author: LUC, 08.01.2003*/
/* Description: */
/* */
IF ( START_AT <= 0 ) THEN START_AT = 1;
IF ( START_AT > 255 ) THEN START_AT = 255;
IF ( NLEN > 255 ) THEN NLEN = 255;
IF ( NLEN < 1 OR NLEN IS NULL ) THEN NLEN = 1;
VGL = '';
RESULT = '';
PFX = '';
IF ( START_AT > 1 ) THEN
BEGIN
II = 1;
WHILE ( II < START_AT ) DO
BEGIN
PFX = PFX || '_';
II = II + 1;
END
END
II = START_AT;
WHILE ( II < NLEN + START_AT ) DO
BEGIN
/* WHAT DOES THE STRING LOOK LIKE AT THE CURRENT POSITION, I.E. QUERY THE CURRENT CHARACTER */
C = ' ';
IF ( SRC LIKE PFX || ' %' ) THEN C = ' ';
ELSE IF ( SRC LIKE PFX || 'A%' ) THEN C = 'A';
ELSE IF ( SRC LIKE PFX || 'B%' ) THEN C = 'B';
ELSE IF ( SRC LIKE PFX || 'C%' ) THEN C = 'C';
ELSE IF ( SRC LIKE PFX || 'D%' ) THEN C = 'D';
ELSE IF ( SRC LIKE PFX || 'E%' ) THEN C = 'E';
ELSE IF ( SRC LIKE PFX || 'F%' ) THEN C = 'F';
ELSE IF ( SRC LIKE PFX || 'G%' ) THEN C = 'G';
ELSE IF ( SRC LIKE PFX || 'H%' ) THEN C = 'H';
ELSE IF ( SRC LIKE PFX || 'I%' ) THEN C = 'I';
ELSE IF ( SRC LIKE PFX || 'J%' ) THEN C = 'J';
ELSE IF ( SRC LIKE PFX || 'K%' ) THEN C = 'K';
ELSE IF ( SRC LIKE PFX || 'L%' ) THEN C = 'L';
ELSE IF ( SRC LIKE PFX || 'M%' ) THEN C = 'M';
ELSE IF ( SRC LIKE PFX || 'N%' ) THEN C = 'N';
ELSE IF ( SRC LIKE PFX || 'O%' ) THEN C = 'O';
ELSE IF ( SRC LIKE PFX || 'P%' ) THEN C = 'P';
ELSE IF ( SRC LIKE PFX || 'Q%' ) THEN C = 'Q';
ELSE IF ( SRC LIKE PFX || 'R%' ) THEN C = 'R';
ELSE IF ( SRC LIKE PFX || 'S%' ) THEN C = 'S';
ELSE IF ( SRC LIKE PFX || 'T%' ) THEN C = 'T';
ELSE IF ( SRC LIKE PFX || 'U%' ) THEN C = 'U';
ELSE IF ( SRC LIKE PFX || 'V%' ) THEN C = 'V';
ELSE IF ( SRC LIKE PFX || 'W%' ) THEN C = 'W';
ELSE IF ( SRC LIKE PFX || 'X%' ) THEN C = 'X';
ELSE IF ( SRC LIKE PFX || 'Y%' ) THEN C = 'Y';
ELSE IF ( SRC LIKE PFX || 'Z%' ) THEN C = 'Z';
ELSE IF ( SRC LIKE PFX || 'a%' ) THEN C = 'a';
ELSE IF ( SRC LIKE PFX || 'b%' ) THEN C = 'b';
ELSE IF ( SRC LIKE PFX || 'c%' ) THEN C = 'c';
ELSE IF ( SRC LIKE PFX || 'd%' ) THEN C = 'd';
ELSE IF ( SRC LIKE PFX || 'e%' ) THEN C = 'e';
ELSE IF ( SRC LIKE PFX || 'f%' ) THEN C = 'f';
ELSE IF ( SRC LIKE PFX || 'g%' ) THEN C = 'g';
ELSE IF ( SRC LIKE PFX || 'h%' ) THEN C = 'h';
ELSE IF ( SRC LIKE PFX || 'i%' ) THEN C = 'i';
ELSE IF ( SRC LIKE PFX || 'j%' ) THEN C = 'j';
ELSE IF ( SRC LIKE PFX || 'k%' ) THEN C = 'k';
ELSE IF ( SRC LIKE PFX || 'l%' ) THEN C = 'l';
ELSE IF ( SRC LIKE PFX || 'm%' ) THEN C = 'm';
ELSE IF ( SRC LIKE PFX || 'n%' ) THEN C = 'n';
ELSE IF ( SRC LIKE PFX || 'o%' ) THEN C = 'o';
ELSE IF ( SRC LIKE PFX || 'p%' ) THEN C = 'p';
ELSE IF ( SRC LIKE PFX || 'q%' ) THEN C = 'q';
ELSE IF ( SRC LIKE PFX || 'r%' ) THEN C = 'r';
ELSE IF ( SRC LIKE PFX || 's%' ) THEN C = 's';
ELSE IF ( SRC LIKE PFX || 't%' ) THEN C = 't';
ELSE IF ( SRC LIKE PFX || 'u%' ) THEN C = 'u';
ELSE IF ( SRC LIKE PFX || 'v%' ) THEN C = 'v';
ELSE IF ( SRC LIKE PFX || 'w%' ) THEN C = 'w';
ELSE IF ( SRC LIKE PFX || 'x%' ) THEN C = 'x';
ELSE IF ( SRC LIKE PFX || 'y%' ) THEN C = 'y';
ELSE IF ( SRC LIKE PFX || 'z%' ) THEN C = 'z';
ELSE IF ( SRC LIKE PFX || '0%' ) THEN C = '0';
ELSE IF ( SRC LIKE PFX || '1%' ) THEN C = '1';
ELSE IF ( SRC LIKE PFX || '2%' ) THEN C = '2';
ELSE IF ( SRC LIKE PFX || '3%' ) THEN C = '3';
ELSE IF ( SRC LIKE PFX || '4%' ) THEN C = '4';
ELSE IF ( SRC LIKE PFX || '5%' ) THEN C = '5';
ELSE IF ( SRC LIKE PFX || '6%' ) THEN C = '6';
ELSE IF ( SRC LIKE PFX || '7%' ) THEN C = '7';
ELSE IF ( SRC LIKE PFX || '8%' ) THEN C = '8';
ELSE IF ( SRC LIKE PFX || '9%' ) THEN C = '9';
ELSE IF ( SRC LIKE PFX || 'ä%' ) THEN C = 'ä';
ELSE IF ( SRC LIKE PFX || 'ö%' ) THEN C = 'ö';
ELSE IF ( SRC LIKE PFX || 'ü%' ) THEN C = 'ü';
ELSE IF ( SRC LIKE PFX || 'Ä%' ) THEN C = 'Ä';
ELSE IF ( SRC LIKE PFX || 'Ö%' ) THEN C = 'Ö';
ELSE IF ( SRC LIKE PFX || 'Ü%' ) THEN C = 'Ü';
ELSE IF ( SRC LIKE PFX || 'ß%' ) THEN C = 'ß';
ELSE IF ( SRC LIKE PFX || '!%' ) THEN C = '!';
ELSE IF ( SRC LIKE PFX || '"%' ) THEN C = '"';
ELSE IF ( SRC LIKE PFX || '§%' ) THEN C = '§';
ELSE IF ( SRC LIKE PFX || '$%' ) THEN C = '$';
ELSE IF ( SRC LIKE PFX || '&%' ) THEN C = '&';
ELSE IF ( SRC LIKE PFX || '/%' ) THEN C = '/';
ELSE IF ( SRC LIKE PFX || '(%' ) THEN C = '(';
ELSE IF ( SRC LIKE PFX || ')%' ) THEN C = ')';
ELSE IF ( SRC LIKE PFX || '=%' ) THEN C = '=';
ELSE IF ( SRC LIKE PFX || '@%' ) THEN C = '@';
ELSE IF ( SRC LIKE PFX || %' ) THEN C = ';
ELSE IF ( SRC LIKE PFX || '*%' ) THEN C = '*';
ELSE IF ( SRC LIKE PFX || '~%' ) THEN C = '~';
ELSE IF ( SRC LIKE PFX || '#%' ) THEN C = '#';
ELSE IF ( SRC LIKE PFX || '%' ) THEN C = '´';
ELSE IF ( SRC LIKE PFX || %' ) THEN C = ';
ELSE IF ( SRC LIKE PFX || 'Á%' ) THEN C = 'Á';
ELSE IF ( SRC LIKE PFX || 'É%' ) THEN C = 'É';
ELSE IF ( SRC LIKE PFX || 'Í%' ) THEN C = 'Í';
ELSE IF ( SRC LIKE PFX || 'Ó%' ) THEN C = 'Ó';
ELSE IF ( SRC LIKE PFX || 'Ú%' ) THEN C = 'Ú';
ELSE IF ( SRC LIKE PFX || 'á%' ) THEN C = 'á';
ELSE IF ( SRC LIKE PFX || 'é%' ) THEN C = 'é';
ELSE IF ( SRC LIKE PFX || 'í%' ) THEN C = 'í';
ELSE IF ( SRC LIKE PFX || 'ó%' ) THEN C = 'ó';
ELSE IF ( SRC LIKE PFX || 'ú%' ) THEN C = 'ú';
ELSE IF ( SRC LIKE PFX || 'À%' ) THEN C = 'À';
ELSE IF ( SRC LIKE PFX || 'È%' ) THEN C = 'È';
ELSE IF ( SRC LIKE PFX || 'Ì%' ) THEN C = 'Ì';
ELSE IF ( SRC LIKE PFX || 'Ò%' ) THEN C = 'Ò';
ELSE IF ( SRC LIKE PFX || 'Ù%' ) THEN C = 'Ù';
ELSE IF ( SRC LIKE PFX || 'à%' ) THEN C = 'à';
ELSE IF ( SRC LIKE PFX || 'è%' ) THEN C = 'è';
ELSE IF ( SRC LIKE PFX || 'ì%' ) THEN C = 'ì';
ELSE IF ( SRC LIKE PFX || 'ò%' ) THEN C = 'ò';
ELSE IF ( SRC LIKE PFX || 'ù%' ) THEN C = 'ù';
ELSE IF ( SRC LIKE PFX || 'Â%' ) THEN C = 'Â';
ELSE IF ( SRC LIKE PFX || 'Ê%' ) THEN C = 'Ê';
ELSE IF ( SRC LIKE PFX || 'Î%' ) THEN C = 'Î';
ELSE IF ( SRC LIKE PFX || 'Ô%' ) THEN C = 'Ô';
ELSE IF ( SRC LIKE PFX || 'Û%' ) THEN C = 'Û';
ELSE IF ( SRC LIKE PFX || 'â%' ) THEN C = 'â';
ELSE IF ( SRC LIKE PFX || 'ê%' ) THEN C = 'ê';
ELSE IF ( SRC LIKE PFX || 'î%' ) THEN C = 'î';
ELSE IF ( SRC LIKE PFX || 'ô%' ) THEN C = 'ô';
ELSE IF ( SRC LIKE PFX || 'û%' ) THEN C = 'û';
ELSE IF ( SRC LIKE PFX || '{%' ) THEN C = '{';
ELSE IF ( SRC LIKE PFX || '}%' ) THEN C = '}';
ELSE IF ( SRC LIKE PFX || '[%' ) THEN C = '[';
ELSE IF ( SRC LIKE PFX || ']%' ) THEN C = ']';
RESULT = RESULT || :C;
PFX = PFX || '_';
II = II + 1;
IF ( II > 255 ) THEN
BEGIN
SUSPEND;
EXIT;
END
END
SUSPEND;
END
See also:
Firebird 2.0 Language Reference Update: SUBSTRING()
Firebird 2.0.4 Release Notes: Built-in function SUBSTRING() enhanced
back to top of page
<< Sample procedure: determining age | IBExpert | Trigger >>
