File: //usr/local/ssl/local/share/man/man3/SQL::Statement::Functions.3
.\" Automatically generated by Pod::Man 2.25 (Pod::Simple 3.20)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings. \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote. \*(C+ will
.\" give a nicer C++. Capital omega is used to do unbreakable dashes and
.\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
. ds -- \(*W-
. ds PI pi
. if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
. if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch
. ds L" ""
. ds R" ""
. ds C` ""
. ds C' ""
'br\}
.el\{\
. ds -- \|\(em\|
. ds PI \(*p
. ds L" ``
. ds R" ''
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD. Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.ie \nF \{\
. de IX
. tm Index:\\$1\t\\n%\t"\\$2"
..
. nr % 0
. rr F
.\}
.el \{\
. de IX
..
.\}
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear. Run. Save yourself. No user-serviceable parts.
. \" fudge factors for nroff and troff
.if n \{\
. ds #H 0
. ds #V .8m
. ds #F .3m
. ds #[ \f1
. ds #] \fP
.\}
.if t \{\
. ds #H ((1u-(\\\\n(.fu%2u))*.13m)
. ds #V .6m
. ds #F 0
. ds #[ \&
. ds #] \&
.\}
. \" simple accents for nroff and troff
.if n \{\
. ds ' \&
. ds ` \&
. ds ^ \&
. ds , \&
. ds ~ ~
. ds /
.\}
.if t \{\
. ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
. ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
. ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
. ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
. ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
. ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
. \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
. \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
. \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
. ds : e
. ds 8 ss
. ds o a
. ds d- d\h'-1'\(ga
. ds D- D\h'-1'\(hy
. ds th \o'bp'
. ds Th \o'LP'
. ds ae ae
. ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "SQL::Statement::Functions 3"
.TH SQL::Statement::Functions 3 "2012-12-19" "perl v5.8.8" "User Contributed Perl Documentation"
.\" For nroff, turn off justification. Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
SQL::Statement::Functions \- built\-in & user\-defined SQL functions
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 4
\& SELECT Func(args);
\& SELECT * FROM Func(args);
\& SELECT * FROM x WHERE Funcs(args);
\& SELECT * FROM x WHERE y < Funcs(args);
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This module contains the built-in functions for SQL::Parser and SQL::Statement. All of the functions are also available in any DBDs that subclass those modules (e.g. \s-1DBD::CSV\s0, \s-1DBD::DBM\s0, DBD::File, DBD::AnyData, DBD::Excel, etc.).
.PP
This documentation covers built-in functions and also explains how to create your own functions to supplement the built-in ones. It's easy. If you create one that is generally useful, see below for how to submit it to become a built-in function.
.SH "Function syntax"
.IX Header "Function syntax"
When using SQL::Statement/SQL::Parser directly to parse \s-1SQL\s0, functions (either built-in or user-defined) may occur anywhere in a \s-1SQL\s0 statement that values, column names, table names, or predicates may occur. When using the modules through a \s-1DBD\s0 or in any other context in which the \s-1SQL\s0 is both parsed and executed, functions can occur in the same places except that they can not occur in the column selection clause of a \s-1SELECT\s0 statement that contains a \s-1FROM\s0 clause.
.PP
.Vb 1
\& # valid for both parsing and executing
\&
\& SELECT MyFunc(args);
\& SELECT * FROM MyFunc(args);
\& SELECT * FROM x WHERE MyFuncs(args);
\& SELECT * FROM x WHERE y < MyFuncs(args);
\&
\& # valid only for parsing (won\*(Aqt work from a DBD)
\&
\& SELECT MyFunc(args) FROM x WHERE y;
.Ve
.SH "User-Defined Functions"
.IX Header "User-Defined Functions"
.SS "Loading User-Defined Functions"
.IX Subsection "Loading User-Defined Functions"
In addition to the built-in functions, you can create any number of your own user-defined functions (UDFs). In order to use a \s-1UDF\s0 in a script, you first have to create a perl subroutine (see below), then you need to make the function available to your database handle with the \s-1CREATE\s0 \s-1FUNCTION\s0 or \s-1LOAD\s0 commands:
.PP
.Vb 2
\& # load a single function "foo" from a subroutine
\& # named "foo" in the current package
\&
\& $dbh\->do(" CREATE FUNCTION foo EXTERNAL ");
\&
\& # load a single function "foo" from a subroutine
\& # named "bar" in the current package
\&
\& $dbh\->do(" CREATE FUNCTION foo EXTERNAL NAME bar");
\&
\&
\& # load a single function "foo" from a subroutine named "foo"
\& # in another package
\&
\& $dbh\->do(\*(Aq CREATE FUNCTION foo EXTERNAL NAME "Bar::Baz::foo" \*(Aq);
\&
\& # load all the functions in another package
\&
\& $dbh\->do(\*(Aq LOAD "Bar::Baz" \*(Aq);
.Ve
.PP
Functions themselves should follow \s-1SQL\s0 identifier naming rules. Subroutines loaded with \s-1CREATE\s0 \s-1FUNCTION\s0 can have any valid perl subroutine name. Subroutines loaded with \s-1LOAD\s0 must start with \s-1SQL_FUNCTION_\s0 and then the actual function name. For example:
.PP
.Vb 5
\& package Qux::Quimble;
\& sub SQL_FUNCTION_FOO { ... }
\& sub SQL_FUNCTION_BAR { ... }
\& sub some_other_perl_subroutine_not_a_function { ... }
\& 1;
\&
\& # in another package
\& $dbh\->do("LOAD Qux::Quimble");
\&
\& # This loads FOO and BAR as SQL functions.
.Ve
.SS "Creating User-Defined Functions"
.IX Subsection "Creating User-Defined Functions"
User-defined functions (UDFs) are perl subroutines that return values appropriate to the context of the function in a \s-1SQL\s0 statement. For example the built-in \s-1CURRENT_TIME\s0 returns a string value and therefore may be used anywhere in a \s-1SQL\s0 statement that a string value can. Here' the entire perl code for the function:
.PP
.Vb 8
\& # CURRENT_TIME
\& #
\& # arguments : none
\& # returns : string containing current time as hh::mm::ss
\& #
\& sub SQL_FUNCTION_CURRENT_TIME {
\& sprintf "%02s::%02s::%02s",(localtime)[2,1,0]
\& }
.Ve
.PP
More complex functions can make use of a number of arguments always passed to functions automatically. Functions always receive these values in \f(CW@_:\fR
.PP
.Vb 3
\& sub FOO {
\& my($self,$sth,@params);
\& }
.Ve
.PP
The first argument, \f(CW$self\fR, is whatever class the function is defined in, not generally useful unless you have an entire module to support the function.
.PP
The second argument, \f(CW$sth\fR is the active statement handle of the current statement. Like all active statement handles it contains the current database handle in the {Database} attribute so you can have access to the database handle in any function:
.PP
.Vb 5
\& sub FOO {
\& my($self,$sth,@params);
\& my $dbh = $sth\->{Database};
\& # $dbh\->do( ...), etc.
\& }
.Ve
.PP
In actual practice you probably want to use \f(CW$sth\fR\->{Database} directly rather than making a local copy, so \f(CW$sth\fR\->{Database}\->do(...).
.PP
The remaining arguments, \f(CW@params\fR, are arguments passed by users to the function, either directly or with placeholders; another silly example which just returns the results of multiplying the arguments passed to it:
.PP
.Vb 4
\& sub MULTIPLY {
\& my($self,$sth,@params);
\& return $params[0] * $params[1];
\& }
\&
\& # first make the function available
\& #
\& $dbh\->do("CREATE FUNCTION MULTIPLY");
\&
\& # then multiply col3 in each row times seven
\& #
\& my $sth=$dbh\->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,7)");
\& $sth\->execute;
\& #
\& # or
\& #
\& my $sth=$dbh\->prepare("SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,?)");
\& $sth\->execute(7);
.Ve
.SS "Creating In-Memory Tables with functions"
.IX Subsection "Creating In-Memory Tables with functions"
A function can return almost anything, as long is it is an appropriate return for the context the function will be used in. In the special case of table-returning functions, the function should return a reference to an array of array references with the first row being the column names and the remaining rows the data. For example:
.PP
\&\fB1. create a function that returns an AoA\fR,
.PP
.Vb 7
\& sub Japh {[
\& [qw( id word )],
\& [qw( 1 Hacker )],
\& [qw( 2 Perl )],
\& [qw( 3 Another )],
\& [qw( 4 Just )],
\& ]}
.Ve
.PP
\&\fB2. make your database handle aware of the function\fR
.PP
.Vb 1
\& $dbh\->do("CREATE FUNCTION \*(AqJaph\*(Aq);
.Ve
.PP
\&\fB3. Access the data in the AoA from \s-1SQL\s0\fR
.PP
.Vb 1
\& $sth = $dbh\->prepare("SELECT word FROM Japh ORDER BY id DESC");
.Ve
.PP
Or here's an example that does a join on two in-memory tables:
.PP
.Vb 4
\& sub Prof {[ [qw(pid pname)],[qw(1 Sue )],[qw(2 Bob)],[qw(3 Tom )] ]}
\& sub Class {[ [qw(pid cname)],[qw(1 Chem)],[qw(2 Bio)],[qw(2 Math)] ]}
\& $dbh\->do("CREATE FUNCTION $_) for qw(Prof Class);
\& $sth = $dbh\->prepare("SELECT * FROM Prof NATURAL JOIN Class");
.Ve
.PP
The \*(L"Prof\*(R" and \*(L"Class\*(R" functions return tables which can be used like any \s-1SQL\s0 table.
.PP
More complex functions might do something like scrape an \s-1RSS\s0 feed, or search a file system and put the results in AoA. For example, to search a directory with \s-1SQL:\s0
.PP
.Vb 10
\& sub Dir {
\& my($self,$sth,$dir)=@_;
\& opendir D, $dir or die "\*(Aq$dir\*(Aq:$!";
\& my @files = readdir D;
\& my $data = [[qw(fileName fileExt)]];
\& for (@files) {
\& my($fn,$ext) = /^(.*)(\e.[^\e.]+)$/;
\& push @$data, [$fn,$ext];
\& }
\& return $data;
\& }
\& $dbh\->do("CREATE FUNCTION Dir");
\& printf "%s\en", join\*(Aq \*(Aq,@{ $dbh\->selectcol_arrayref("
\& SELECT fileName FROM Dir(\*(Aq./\*(Aq) WHERE fileExt = \*(Aq.pl\*(Aq
\& ")};
.Ve
.PP
Obviously, that function could be expanded with File::Find and/or stat to provide more information and it could be made to accept a list of directories rather than a single directory.
.PP
Table-Returning functions are a way to turn *anything* that can be modeled as an AoA into a \s-1DBI\s0 data source.
.SH "Built-in Functions"
.IX Header "Built-in Functions"
.SS "\s-1SQL\-92/ODBC\s0 Compatibility"
.IX Subsection "SQL-92/ODBC Compatibility"
All \s-1ODBC\s0 3.0 functions are available except for the following:
.PP
.Vb 1
\& ### SQL\-92 / ODBC Functions
\&
\& # CONVERT / CAST \- Complex to implement, but a draft is in the works.
\& # DIFFERENCE \- Function is not clearly defined in spec and has very limited applications
\& # EXTRACT \- Contains a FROM keyword and requires rather freeform datetime/interval expression
\&
\& ### ODBC 3.0 Time/Date Functions only
\&
\& # DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, QUARTER, SECOND, TIMESTAMPDIFF,
\& # WEEK, YEAR \- Requires freeform datetime/interval expressions. In a later release, these could
\& # be implemented with the help of Date::Parse.
.Ve
.PP
\&\s-1ODBC\s0 3.0 functions that are implemented with differences include:
.PP
.Vb 8
\& # SOUNDEX \- Returns true/false, instead of a SOUNDEX code
\& # RAND \- Seed value is a second parameter with a new first parameter for max limit
\& # LOG \- Returns base X (or 10) log of number, not natural log. LN is used for natural log, and
\& # LOG10 is still available for standards compatibility.
\& # POSITION \- Does not use \*(AqIN\*(Aq keyword; cannot be fixed as previous versions of SQL::Statement defined
\& # the function as such.
\& # REPLACE / SUBSTITUTE \- Uses a regular expression string for the second parameter, replacing the last two
\& # parameters of the typical ODBC function
.Ve
.SS "Aggregate Functions"
.IX Subsection "Aggregate Functions"
\fI\s-1MIN\s0, \s-1MAX\s0, \s-1AVG\s0, \s-1SUM\s0, \s-1COUNT\s0\fR
.IX Subsection "MIN, MAX, AVG, SUM, COUNT"
.PP
Aggregate functions are handled elsewhere, see SQL::Parser for documentation.
.SS "Date and Time Functions"
.IX Subsection "Date and Time Functions"
These functions can be used without parentheses.
.PP
\fI\s-1CURRENT_DATE\s0 aka \s-1CURDATE\s0\fR
.IX Subsection "CURRENT_DATE aka CURDATE"
.PP
.Vb 3
\& # purpose : find current date
\& # arguments : none
\& # returns : string containing current date as yyyy\-mm\-dd
.Ve
.PP
\fI\s-1CURRENT_TIME\s0 aka \s-1CURTIME\s0\fR
.IX Subsection "CURRENT_TIME aka CURTIME"
.PP
.Vb 3
\& # purpose : find current time
\& # arguments : optional seconds precision
\& # returns : string containing current time as hh:mm:ss (or ss.sss...)
.Ve
.PP
\fI\s-1CURRENT_TIMESTAMP\s0 aka \s-1NOW\s0\fR
.IX Subsection "CURRENT_TIMESTAMP aka NOW"
.PP
.Vb 3
\& # purpose : find current date and time
\& # arguments : optional seconds precision
\& # returns : string containing current timestamp as yyyy\-mm\-dd hh:mm:ss (or ss.sss...)
.Ve
.PP
\fI\s-1UNIX_TIMESTAMP\s0\fR
.IX Subsection "UNIX_TIMESTAMP"
.PP
.Vb 3
\& # purpose : find the current time in UNIX epoch format
\& # arguments : optional seconds precision (unlike the MySQL version)
\& # returns : a (64\-bit) number, possibly with decimals
.Ve
.SS "String Functions"
.IX Subsection "String Functions"
\fI\s-1ASCII\s0 & \s-1CHAR\s0\fR
.IX Subsection "ASCII & CHAR"
.PP
.Vb 2
\& # purpose : same as ord and chr, respectively (NULL for any NULL args)
\& # arguments : string or character (or number for CHAR); CHAR can have any amount of numbers for a string
.Ve
.PP
\fI\s-1BIT_LENGTH\s0\fR
.IX Subsection "BIT_LENGTH"
.PP
.Vb 2
\& # purpose : length of the string in bits
\& # arguments : string
.Ve
.PP
\fI\s-1CHARACTER_LENGTH\s0 aka \s-1CHAR_LENGTH\s0\fR
.IX Subsection "CHARACTER_LENGTH aka CHAR_LENGTH"
.PP
.Vb 3
\& # purpose : find length in characters of a string
\& # arguments : a string
\& # returns : a number \- the length of the string in characters
.Ve
.PP
\fI\s-1COALESCE\s0 aka \s-1NVL\s0 aka \s-1IFNULL\s0\fR
.IX Subsection "COALESCE aka NVL aka IFNULL"
.PP
.Vb 5
\& # purpose : return the first non\-NULL value from a list
\& # arguments : 1 or more expressions
\& # returns : the first expression (reading left to right)
\& # which is not NULL; returns NULL if all are NULL
\& #
.Ve
.PP
\fI\s-1CONCAT\s0\fR
.IX Subsection "CONCAT"
.PP
.Vb 8
\& # purpose : concatenate 1 or more strings into a single string;
\& # an alternative to the \*(Aq||\*(Aq operator
\& # arguments : 1 or more strings
\& # returns : the concatenated string
\& #
\& # example : SELECT CONCAT(first_string, \*(Aqthis string\*(Aq, \*(Aq that string\*(Aq)
\& # returns "<value\-of\-first\-string>this string that string"
\& # note : if any argument evaluates to NULL, the returned value is NULL
.Ve
.PP
\fI\s-1CONV\s0\fR
.IX Subsection "CONV"
.PP
.Vb 10
\& # purpose : convert a number X from base Y to base Z (from base 2 to 92)
\& # arguments : X (can by a number or string depending on the base), Y, Z (Z defaults to 10)
\& # returns : either a string or number, in base Z
\& # notes :
\& # * Supports negative and decimal numbers
\& # * Will use big numbers if it has to, so accuracy is at near absolute levels
\& # * Letters are case\-sensitive after base 36
\& # * Base character sets are: (second set is for compatibility with base 64)
\& # 2 to 62 = 0\-9, A\-Z, a\-z
\& # 62 to 92 = A\-Z, a\-z, 0\-9, +/_=~|,;:?!@#$%^&*()<>{}[]\e\`\*(Aq"
.Ve
.PP
\fI\s-1DECODE\s0\fR
.IX Subsection "DECODE"
.PP
.Vb 10
\& # purpose : compare the first argument against
\& # succeding arguments at position 1 + 2N
\& # (N = 0 to (# of arguments \- 2)/2), and if equal,
\& # return the value of the argument at 1 + 2N + 1; if no
\& # arguments are equal, the last argument value is returned
\& # arguments : 4 or more expressions, must be even # of arguments
\& # returns : the value of the argument at 1 + 2N + 1 if argument 1 + 2N
\& # is equal to argument1; else the last argument value
\& #
\& # example : SELECT DECODE(some_column,
\& # \*(Aqfirst value\*(Aq, \*(Aqfirst value matched\*(Aq
\& # \*(Aq2nd value\*(Aq, \*(Aq2nd value matched\*(Aq
\& # \*(Aqno value matched\*(Aq
\& # )
.Ve
.PP
\fI\s-1INSERT\s0\fR
.IX Subsection "INSERT"
.PP
.Vb 3
\& # purpose : string where L characters have been deleted from STR1, beginning at S,
\& # and where STR2 has been inserted into STR1, beginning at S. NULL for any NULL args.
\& # arguments : STR1, S, L, STR2
.Ve
.PP
\fI\s-1HEX\s0 & \s-1OCT\s0 & \s-1BIN\s0\fR
.IX Subsection "HEX & OCT & BIN"
.PP
.Vb 2
\& # purpose : convert number X from decimal to hex/octal/binary; equiv. to CONV(X, 10, 16/8/2)
\& # arguments : X
.Ve
.PP
\fI\s-1LEFT\s0 & \s-1RIGHT\s0\fR
.IX Subsection "LEFT & RIGHT"
.PP
.Vb 2
\& # purpose : leftmost or rightmost L characters in STR, or NULL for any NULL args
\& # arguments : STR1, L
.Ve
.PP
\fI\s-1LOCATE\s0 aka \s-1POSITION\s0\fR
.IX Subsection "LOCATE aka POSITION"
.PP
.Vb 3
\& # purpose : starting position (one\-based) of the first occurrence of STR1
\& within STR2; 0 if it doesn\*(Aqt occur and NULL for any NULL args
\& # arguments : STR1, STR2, and an optional S (starting position to search)
.Ve
.PP
\fI\s-1LOWER\s0 & \s-1UPPER\s0 aka \s-1LCASE\s0 & \s-1UCASE\s0\fR
.IX Subsection "LOWER & UPPER aka LCASE & UCASE"
.PP
.Vb 3
\& # purpose : lower\-case or upper\-case a string
\& # arguments : a string
\& # returns : the sting lower or upper cased
.Ve
.PP
\fI\s-1LTRIM\s0 & \s-1RTRIM\s0\fR
.IX Subsection "LTRIM & RTRIM"
.PP
.Vb 2
\& # purpose : left/right counterparts for TRIM
\& # arguments : string
.Ve
.PP
\fI\s-1OCTET_LENGTH\s0\fR
.IX Subsection "OCTET_LENGTH"
.PP
.Vb 2
\& # purpose : length of the string in bytes (not characters)
\& # arguments : string
.Ve
.PP
\fI\s-1REGEX\s0\fR
.IX Subsection "REGEX"
.PP
.Vb 6
\& # purpose : test if a string matches a perl regular expression
\& # arguments : a string and a regex to match the string against
\& # returns : boolean value of the regex match
\& #
\& # example : ... WHERE REGEX(col3,\*(Aq/^fun/i\*(Aq) ... matches rows
\& # in which col3 starts with "fun", ignoring case
.Ve
.PP
\fI\s-1REPEAT\s0\fR
.IX Subsection "REPEAT"
.PP
.Vb 2
\& # purpose : string composed of STR1 repeated C times, or NULL for any NULL args
\& # arguments : STR1, C
.Ve
.PP
\fI\s-1REPLACE\s0 aka \s-1SUBSTITUTE\s0\fR
.IX Subsection "REPLACE aka SUBSTITUTE"
.PP
.Vb 7
\& # purpose : perform perl subsitution on input string
\& # arguments : a string and a substitute pattern string
\& # returns : the result of the substitute operation
\& #
\& # example : ... WHERE REPLACE(col3,\*(Aqs/fun(\ew+)nier/$1/ig\*(Aq) ... replaces
\& # all instances of /fun(\ew+)nier/ in col3 with the string
\& # between \*(Aqfun\*(Aq and \*(Aqnier\*(Aq
.Ve
.PP
\fI\s-1SOUNDEX\s0\fR
.IX Subsection "SOUNDEX"
.PP
.Vb 6
\& # purpose : test if two strings have matching soundex codes
\& # arguments : two strings
\& # returns : true if the strings share the same soundex code
\& #
\& # example : ... WHERE SOUNDEX(col3,\*(Aqfun\*(Aq) ... matches rows
\& # in which col3 is a soundex match for "fun"
.Ve
.PP
\fI\s-1SPACE\s0\fR
.IX Subsection "SPACE"
.PP
.Vb 2
\& # purpose : a string of spaces
\& # arguments : number of spaces
.Ve
.PP
\fI\s-1SUBSTRING\s0\fR
.IX Subsection "SUBSTRING"
.PP
.Vb 1
\& SUBSTRING( string FROM start_pos [FOR length] )
.Ve
.PP
Returns the substring starting at start_pos and extending for
\&\*(L"length\*(R" character or until the end of the string, if no
\&\*(L"length\*(R" is supplied. Examples:
.PP
.Vb 1
\& SUBSTRING( \*(Aqfoobar\*(Aq FROM 4 ) # returns "bar"
\&
\& SUBSTRING( \*(Aqfoobar\*(Aq FROM 4 FOR 2) # returns "ba"
.Ve
.PP
Note: The \s-1SUBSTRING\s0 function is implemented in SQL::Parser and SQL::Statement and, at the current time, can not be over-ridden.
.PP
\fI\s-1SUBSTR\s0\fR
.IX Subsection "SUBSTR"
.PP
.Vb 3
\& # purpose : same as SUBSTRING, except with comma\-delimited params, instead of
\& words (NULL for any NULL args)
\& # arguments : string, start_pos, [length]
.Ve
.PP
\fI\s-1TRANSLATE\s0\fR
.IX Subsection "TRANSLATE"
.PP
.Vb 3
\& # purpose : transliteration; replace a set of characters in a string with another
\& set of characters (a la tr///), or NULL for any NULL args
\& # arguments : string, string to replace, replacement string
.Ve
.PP
\fI\s-1TRIM\s0\fR
.IX Subsection "TRIM"
.PP
.Vb 1
\& TRIM ( [ [LEADING|TRAILING|BOTH] [\*(Aqtrim_char\*(Aq] FROM ] string )
.Ve
.PP
Removes all occurrences of <trim_char> from the front, back, or
both sides of a string.
.PP
.Vb 1
\& BOTH is the default if neither LEADING nor TRAILING is specified.
\&
\& Space is the default if no trim_char is specified.
\&
\& Examples:
\&
\& TRIM( string )
\& trims leading and trailing spaces from string
\&
\& TRIM( LEADING FROM str )
\& trims leading spaces from string
\&
\& TRIM( \*(Aqx\*(Aq FROM str )
\& trims leading and trailing x\*(Aqs from string
.Ve
.PP
Note: The \s-1TRIM\s0 function is implemented in SQL::Parser and SQL::Statement and, at the current time, can not be over-ridden.
.PP
\fI\s-1UNHEX\s0\fR
.IX Subsection "UNHEX"
.PP
.Vb 2
\& # purpose : convert each pair of hexadecimal digits to a byte (or a Unicode character)
\& # arguments : string of hex digits, with an optional encoding name of the data string
.Ve
.SS "Numeric Functions"
.IX Subsection "Numeric Functions"
\fI\s-1ABS\s0\fR
.IX Subsection "ABS"
.PP
.Vb 2
\& # purpose : find the absolute value of a given numeric expression
\& # arguments : numeric expression
.Ve
.PP
\fI\s-1CEILING\s0 (aka \s-1CEIL\s0) & \s-1FLOOR\s0\fR
.IX Subsection "CEILING (aka CEIL) & FLOOR"
.PP
.Vb 2
\& # purpose : rounds up/down to the nearest integer
\& # arguments : numeric expression
.Ve
.PP
\fI\s-1EXP\s0\fR
.IX Subsection "EXP"
.PP
.Vb 2
\& # purpose : raise e to the power of a number
\& # arguments : numeric expression
.Ve
.PP
\fI\s-1LOG\s0\fR
.IX Subsection "LOG"
.PP
.Vb 2
\& # purpose : base B logarithm of X
\& # arguments : B, X or just one argument of X for base 10
.Ve
.PP
\fI\s-1LN\s0 & \s-1LOG10\s0\fR
.IX Subsection "LN & LOG10"
.PP
.Vb 2
\& # purpose : natural logarithm (base e) or base 10 of X
\& # arguments : numeric expression
.Ve
.PP
\fI\s-1MOD\s0\fR
.IX Subsection "MOD"
.PP
.Vb 2
\& # purpose : modulus, or remainder, left over from dividing X / Y
\& # arguments : X, Y
.Ve
.PP
\fI\s-1POWER\s0 aka \s-1POW\s0\fR
.IX Subsection "POWER aka POW"
.PP
.Vb 2
\& # purpose : X to the power of Y
\& # arguments : X, Y
.Ve
.PP
\fI\s-1RAND\s0\fR
.IX Subsection "RAND"
.PP
.Vb 2
\& # purpose : random fractional number greater than or equal to 0 and less than the value of X
\& # arguments : X (with optional seed value of Y)
.Ve
.PP
\fI\s-1ROUND\s0\fR
.IX Subsection "ROUND"
.PP
.Vb 2
\& # purpose : round X with Y number of decimal digits (precision)
\& # arguments : X, optional Y defaults to 0
.Ve
.PP
\fI\s-1SIGN\s0\fR
.IX Subsection "SIGN"
.PP
.Vb 2
\& # purpose : returns \-1, 0, 1, NULL for negative, 0, positive, NULL values, respectively
\& # arguments : numeric expression
.Ve
.PP
\fI\s-1SQRT\s0\fR
.IX Subsection "SQRT"
.PP
.Vb 2
\& # purpose : square root of X
\& # arguments : X
.Ve
.PP
\fI\s-1TRUNCATE\s0 aka \s-1TRUNC\s0\fR
.IX Subsection "TRUNCATE aka TRUNC"
.PP
.Vb 2
\& # purpose : similar to ROUND, but removes the decimal
\& # arguments : X, optional Y defaults to 0
.Ve
.SS "Trigonometric Functions"
.IX Subsection "Trigonometric Functions"
All of these functions work exactly like their counterparts in Math::Trig; go there for documentation.
.IP "\s-1ACOS\s0" 4
.IX Item "ACOS"
.PD 0
.IP "\s-1ACOSEC\s0" 4
.IX Item "ACOSEC"
.IP "\s-1ACOSECH\s0" 4
.IX Item "ACOSECH"
.IP "\s-1ACOSH\s0" 4
.IX Item "ACOSH"
.IP "\s-1ACOT\s0" 4
.IX Item "ACOT"
.IP "\s-1ACOTAN\s0" 4
.IX Item "ACOTAN"
.IP "\s-1ACOTANH\s0" 4
.IX Item "ACOTANH"
.IP "\s-1ACOTH\s0" 4
.IX Item "ACOTH"
.IP "\s-1ACSC\s0" 4
.IX Item "ACSC"
.IP "\s-1ACSCH\s0" 4
.IX Item "ACSCH"
.IP "\s-1ASEC\s0" 4
.IX Item "ASEC"
.IP "\s-1ASECH\s0" 4
.IX Item "ASECH"
.IP "\s-1ASIN\s0" 4
.IX Item "ASIN"
.IP "\s-1ASINH\s0" 4
.IX Item "ASINH"
.IP "\s-1ATAN\s0" 4
.IX Item "ATAN"
.IP "\s-1ATANH\s0" 4
.IX Item "ATANH"
.IP "\s-1COS\s0" 4
.IX Item "COS"
.IP "\s-1COSEC\s0" 4
.IX Item "COSEC"
.IP "\s-1COSECH\s0" 4
.IX Item "COSECH"
.IP "\s-1COSH\s0" 4
.IX Item "COSH"
.IP "\s-1COT\s0" 4
.IX Item "COT"
.IP "\s-1COTAN\s0" 4
.IX Item "COTAN"
.IP "\s-1COTANH\s0" 4
.IX Item "COTANH"
.IP "\s-1COTH\s0" 4
.IX Item "COTH"
.IP "\s-1CSC\s0" 4
.IX Item "CSC"
.IP "\s-1CSCH\s0" 4
.IX Item "CSCH"
.IP "\s-1SEC\s0" 4
.IX Item "SEC"
.IP "\s-1SECH\s0" 4
.IX Item "SECH"
.IP "\s-1SIN\s0" 4
.IX Item "SIN"
.IP "\s-1SINH\s0" 4
.IX Item "SINH"
.IP "\s-1TAN\s0" 4
.IX Item "TAN"
.IP "\s-1TANH\s0" 4
.IX Item "TANH"
.PD
Takes a single parameter. All of Math::Trig's aliases are included.
.IP "\s-1ATAN2\s0" 4
.IX Item "ATAN2"
The y,x version of arc tangent.
.IP "\s-1DEG2DEG\s0" 4
.IX Item "DEG2DEG"
.PD 0
.IP "\s-1DEG2GRAD\s0" 4
.IX Item "DEG2GRAD"
.IP "\s-1DEG2RAD\s0" 4
.IX Item "DEG2RAD"
.PD
Converts out-of-bounds values into its correct range.
.IP "\s-1GRAD2DEG\s0" 4
.IX Item "GRAD2DEG"
.PD 0
.IP "\s-1GRAD2GRAD\s0" 4
.IX Item "GRAD2GRAD"
.IP "\s-1GRAD2RAD\s0" 4
.IX Item "GRAD2RAD"
.IP "\s-1RAD2DEG\s0" 4
.IX Item "RAD2DEG"
.IP "\s-1RAD2GRAD\s0" 4
.IX Item "RAD2GRAD"
.IP "\s-1RAD2RAD\s0" 4
.IX Item "RAD2RAD"
.PD
Like their Math::Trig's counterparts, accepts an optional 2nd boolean parameter (like \fB\s-1TRUE\s0\fR) to keep prevent range wrapping.
.IP "\s-1DEGREES\s0" 4
.IX Item "DEGREES"
.PD 0
.IP "\s-1RADIANS\s0" 4
.IX Item "RADIANS"
.PD
\&\fB\s-1DEGREES\s0\fR and \fB\s-1RADIANS\s0\fR are included for \s-1SQL\-92\s0 compatibility, and map to \fB\s-1RAD2DEG\s0\fR and \fB\s-1DEG2RAD\s0\fR, respectively.
.IP "\s-1PI\s0" 4
.IX Item "PI"
\&\fB\s-1PI\s0\fR can be used without parentheses.
.SS "System Functions"
.IX Subsection "System Functions"
\fI\s-1DBNAME\s0 & \s-1USERNAME\s0 (aka \s-1USER\s0)\fR
.IX Subsection "DBNAME & USERNAME (aka USER)"
.PP
.Vb 2
\& # purpose : name of the database / username
\& # arguments : none
.Ve
.SS "Special Utility Functions"
.IX Subsection "Special Utility Functions"
\fI\s-1IMPORT\s0\fR
.IX Subsection "IMPORT"
.PP
.Vb 2
\& CREATE TABLE foo AS IMPORT(?) ,{},$external_executed_sth
\& CREATE TABLE foo AS IMPORT(?) ,{},$AoA
.Ve
.PP
\fI\s-1RUN\s0\fR
.IX Subsection "RUN"
.PP
Takes the name of a file containing \s-1SQL\s0 statements and runs the statements; see
SQL::Parser for documentation.
.SH "Submitting built-in functions"
.IX Header "Submitting built-in functions"
If you make a generally useful \s-1UDF\s0, why not submit it to me and have it (and your name) included with the built-in functions? Please follow the format shown in the module including a description of the arguments and return values for the function as well as an example. Send them to the dbi\-dev@perl.org mailing list (see <http://dbi.perl.org>).
.PP
Thanks in advance :\-).
.SH "ACKNOWLEDGEMENTS"
.IX Header "ACKNOWLEDGEMENTS"
Dean Arnold supplied \s-1DECODE\s0, \s-1COALESCE\s0, \s-1REPLACE\s0, many thanks!
Brendan Byrd added in the Numeric/Trig/System functions and filled in the \s-1SQL92/ODBC\s0 gaps for the date/string functions.
.SH "AUTHOR & COPYRIGHT"
.IX Header "AUTHOR & COPYRIGHT"
Copyright (c) 2005 by Jeff Zucker: jzuckerATcpan.org
Copyright (c) 2009,2010 by Jens Rehsack: rehsackATcpan.org
.PP
All rights reserved.
.PP
The module may be freely distributed under the same terms as
Perl itself using either the \*(L"\s-1GPL\s0 License\*(R" or the \*(L"Artistic
License\*(R" as specified in the Perl \s-1README\s0 file.