{
    "mode": "perldoc",
    "parameter": "SQL::Statement::Functions",
    "section": "",
    "url": "https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3AFunctions/json",
    "generated": "2026-06-14T00:50:30Z",
    "synopsis": "SELECT Func(args);\nSELECT * FROM Func(args);\nSELECT * FROM x WHERE Funcs(args);\nSELECT * FROM x WHERE y < Funcs(args);",
    "sections": {
        "NAME": {
            "content": "SQL::Statement::Functions - built-in & user-defined SQL functions\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "SELECT Func(args);\nSELECT * FROM Func(args);\nSELECT * FROM x WHERE Funcs(args);\nSELECT * FROM x WHERE y < Funcs(args);\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "This module contains the built-in functions for SQL::Parser and SQL::Statement. All of the\nfunctions are also available in any DBDs that subclass those modules (e.g. DBD::CSV, DBD::DBM,\nDBD::File, DBD::AnyData, DBD::Excel, etc.).\n\nThis documentation covers built-in functions and also explains how to create your own functions\nto supplement the built-in ones. It's easy. If you create one that is generally useful, see\nbelow for how to submit it to become a built-in function.\n",
            "subsections": []
        },
        "Function syntax": {
            "content": "When using SQL::Statement/SQL::Parser directly to parse SQL, functions (either built-in or\nuser-defined) may occur anywhere in a SQL statement that values, column names, table names, or\npredicates may occur. When using the modules through a DBD or in any other context in which the\nSQL is both parsed and executed, functions can occur in the same places except that they can not\noccur in the column selection clause of a SELECT statement that contains a FROM clause.\n\n# valid for both parsing and executing\n\nSELECT MyFunc(args);\nSELECT * FROM MyFunc(args);\nSELECT * FROM x WHERE MyFuncs(args);\nSELECT * FROM x WHERE y < MyFuncs(args);\n\n# valid only for parsing (won't work from a DBD)\n\nSELECT MyFunc(args) FROM x WHERE y;\n",
            "subsections": []
        },
        "User-Defined Functions": {
            "content": "",
            "subsections": [
                {
                    "name": "Loading User-Defined Functions",
                    "content": "In addition to the built-in functions, you can create any number of your own user-defined\nfunctions (UDFs). In order to use a UDF in a script, you first have to create a perl subroutine\n(see below), then you need to make the function available to your database handle with the\nCREATE FUNCTION or LOAD commands:\n\n# load a single function \"foo\" from a subroutine\n# named \"foo\" in the current package\n\n$dbh->do(\" CREATE FUNCTION foo EXTERNAL \");\n\n# load a single function \"foo\" from a subroutine\n# named \"bar\" in the current package\n\n$dbh->do(\" CREATE FUNCTION foo EXTERNAL NAME bar\");\n\n\n# load a single function \"foo\" from a subroutine named \"foo\"\n# in another package\n\n$dbh->do(' CREATE FUNCTION foo EXTERNAL NAME \"Bar::Baz::foo\" ');\n\n# load all the functions in another package\n\n$dbh->do(' LOAD \"Bar::Baz\" ');\n\nFunctions themselves should follow SQL identifier naming rules. Subroutines loaded with CREATE\nFUNCTION can have any valid perl subroutine name. Subroutines loaded with LOAD must start with\nSQLFUNCTION and then the actual function name. For example:\n\npackage Qux::Quimble;\nsub SQLFUNCTIONFOO { ... }\nsub SQLFUNCTIONBAR { ... }\nsub someotherperlsubroutinenotafunction { ... }\n1;\n\n# in another package\n$dbh->do(\"LOAD Qux::Quimble\");\n\n# This loads FOO and BAR as SQL functions.\n"
                },
                {
                    "name": "Creating User-Defined Functions",
                    "content": "User-defined functions (UDFs) are perl subroutines that return values appropriate to the context\nof the function in a SQL statement. For example the built-in CURRENTTIME returns a string value\nand therefore may be used anywhere in a SQL statement that a string value can. Here' the entire\nperl code for the function:\n\n# CURRENTTIME\n#\n# arguments : none\n# returns   : string containing current time as hh::mm::ss\n#\nsub SQLFUNCTIONCURRENTTIME {\nsprintf \"%02s::%02s::%02s\",(localtime)[2,1,0]\n}\n\nMore complex functions can make use of a number of arguments always passed to functions\nautomatically. Functions always receive these values in @:\n\nsub FOO {\nmy($self,$sth,@params);\n}\n\nThe first argument, $self, is whatever class the function is defined in, not generally useful\nunless you have an entire module to support the function.\n\nThe second argument, $sth is the active statement handle of the current statement. Like all\nactive statement handles it contains the current database handle in the {Database} attribute so\nyou can have access to the database handle in any function:\n\nsub FOO {\nmy($self,$sth,@params);\nmy $dbh = $sth->{Database};\n# $dbh->do( ...), etc.\n}\n\nIn actual practice you probably want to use $sth->{Database} directly rather than making a local\ncopy, so $sth->{Database}->do(...).\n\nThe remaining arguments, @params, are arguments passed by users to the function, either directly\nor with placeholders; another silly example which just returns the results of multiplying the\narguments passed to it:\n\nsub MULTIPLY {\nmy($self,$sth,@params);\nreturn $params[0] * $params[1];\n}\n\n# first make the function available\n#\n$dbh->do(\"CREATE FUNCTION MULTIPLY\");\n\n# then multiply col3 in each row times seven\n#\nmy $sth=$dbh->prepare(\"SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,7)\");\n$sth->execute;\n#\n# or\n#\nmy $sth=$dbh->prepare(\"SELECT col1 FROM tbl1 WHERE col2 = MULTIPLY(col3,?)\");\n$sth->execute(7);\n"
                },
                {
                    "name": "Creating In-Memory Tables with functions",
                    "content": "A function can return almost anything, as long is it is an appropriate return for the context\nthe function will be used in. In the special case of table-returning functions, the function\nshould return a reference to an array of array references with the first row being the column\nnames and the remaining rows the data. For example:\n\n1. create a function that returns an AoA,\n\nsub Japh {[\n[qw( id word   )],\n[qw( 1 Hacker  )],\n[qw( 2 Perl    )],\n[qw( 3 Another )],\n[qw( 4 Just    )],\n]}\n\n2. make your database handle aware of the function\n\n$dbh->do(\"CREATE FUNCTION 'Japh');\n\n3. Access the data in the AoA from SQL\n\n$sth = $dbh->prepare(\"SELECT word FROM Japh ORDER BY id DESC\");\n\nOr here's an example that does a join on two in-memory tables:\n\nsub Prof  {[ [qw(pid pname)],[qw(1 Sue )],[qw(2 Bob)],[qw(3 Tom )] ]}\nsub Class {[ [qw(pid cname)],[qw(1 Chem)],[qw(2 Bio)],[qw(2 Math)] ]}\n$dbh->do(\"CREATE FUNCTION $) for qw(Prof Class);\n$sth = $dbh->prepare(\"SELECT * FROM Prof NATURAL JOIN Class\");\n\nThe \"Prof\" and \"Class\" functions return tables which can be used like any SQL table.\n\nMore complex functions might do something like scrape an RSS feed, or search a file system and\nput the results in AoA. For example, to search a directory with SQL:\n\nsub Dir {\nmy($self,$sth,$dir)=@;\nopendir D, $dir or die \"'$dir':$!\";\nmy @files = readdir D;\nmy $data = [[qw(fileName fileExt)]];\nfor (@files) {\nmy($fn,$ext) = /^(.*)(\\.[^\\.]+)$/;\npush @$data, [$fn,$ext];\n}\nreturn $data;\n}\n$dbh->do(\"CREATE FUNCTION Dir\");\nprintf \"%s\\n\", join'   ',@{ $dbh->selectcolarrayref(\"\nSELECT fileName FROM Dir('./') WHERE fileExt = '.pl'\n\")};\n\nObviously, that function could be expanded with File::Find and/or stat to provide more\ninformation and it could be made to accept a list of directories rather than a single directory.\n\nTable-Returning functions are a way to turn *anything* that can be modeled as an AoA into a DBI\ndata source.\n"
                }
            ]
        },
        "Built-in Functions": {
            "content": "SQL-92/ODBC Compatibility\nAll ODBC 3.0 functions are available except for the following:\n\n### SQL-92 / ODBC Functions\n\n# CONVERT / CAST - Complex to implement, but a draft is in the works.\n# DIFFERENCE     - Function is not clearly defined in spec and has very limited applications\n# EXTRACT        - Contains a FROM keyword and requires rather freeform datetime/interval expression\n\n### ODBC 3.0 Time/Date Functions only\n\n# DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, QUARTER, SECOND, TIMESTAMPDIFF,\n#    WEEK, YEAR - Requires freeform datetime/interval expressions.  In a later release, these could\n#                    be implemented with the help of Date::Parse.\n\nODBC 3.0 functions that are implemented with differences include:\n\n# SOUNDEX  - Returns true/false, instead of a SOUNDEX code\n# RAND     - Seed value is a second parameter with a new first parameter for max limit\n# LOG      - Returns base X (or 10) log of number, not natural log.  LN is used for natural log, and\n#               LOG10 is still available for standards compatibility.\n# POSITION - Does not use 'IN' keyword; cannot be fixed as previous versions of SQL::Statement defined\n#               the function as such.\n# REPLACE / SUBSTITUTE - Uses a regular expression string for the second parameter, replacing the last two\n#                           parameters of the typical ODBC function\n",
            "subsections": [
                {
                    "name": "Aggregate Functions",
                    "content": "MIN, MAX, AVG, SUM, COUNT\nAggregate functions are handled elsewhere, see SQL::Parser for documentation.\n"
                },
                {
                    "name": "Date and Time Functions",
                    "content": "These functions can be used without parentheses.\n\nCURRENTDATE aka CURDATE\n# purpose   : find current date\n# arguments : none\n# returns   : string containing current date as yyyy-mm-dd\n\nCURRENTTIME aka CURTIME\n# purpose   : find current time\n# arguments : optional seconds precision\n# returns   : string containing current time as hh:mm:ss (or ss.sss...)\n\nCURRENTTIMESTAMP aka NOW\n# purpose   : find current date and time\n# arguments : optional seconds precision\n# returns   : string containing current timestamp as yyyy-mm-dd hh:mm:ss (or ss.sss...)\n\nUNIXTIMESTAMP\n# purpose   : find the current time in UNIX epoch format\n# arguments : optional seconds precision (unlike the MySQL version)\n# returns   : a (64-bit) number, possibly with decimals\n"
                },
                {
                    "name": "String Functions",
                    "content": "ASCII & CHAR\n# purpose   : same as ord and chr, respectively (NULL for any NULL args)\n# arguments : string or character (or number for CHAR); CHAR can have any amount of numbers for a string\n\nBITLENGTH\n# purpose   : length of the string in bits\n# arguments : string\n\nCHARACTERLENGTH aka CHARLENGTH\n# purpose   : find length in characters of a string\n# arguments : a string\n# returns   : a number - the length of the string in characters\n\nCOALESCE aka NVL aka IFNULL\n# purpose   : return the first non-NULL value from a list\n# arguments : 1 or more expressions\n# returns   : the first expression (reading left to right)\n#             which is not NULL; returns NULL if all are NULL\n#\n\nCONCAT\n# purpose   : concatenate 1 or more strings into a single string;\n#                      an alternative to the '||' operator\n# arguments : 1 or more strings\n# returns   : the concatenated string\n#\n# example   : SELECT CONCAT(firststring, 'this string', ' that string')\n#              returns \"<value-of-first-string>this string that string\"\n# note      : if any argument evaluates to NULL, the returned value is NULL\n\nCONV\n# purpose   : convert a number X from base Y to base Z (from base 2 to 64)\n# arguments : X (can by a number or string depending on the base), Y, Z (Z defaults to 10)\nValid bases for Y and Z are: 2, 8, 10, 16 and 64\n# returns   : either a string or number, in base Z\n# notes     : Behavioral table\n#\n#      base | valuation\n#     ------+-----------\n#         2 | binary, base 2 - (0,1)\n#         8 | octal, base 8 - (0..7)\n#        10 | decimal, base 10 - (0..9)\n#        16 | hexadecimal, base 16 - (0..9,a..f)\n#        64 | 0-63 from MIME::Base64\n#\n\nDECODE\n# purpose   : compare the first argument against\n#             succeeding arguments at position 1 + 2N\n#             (N = 0 to (# of arguments - 2)/2), and if equal,\n#                              return the value of the argument at 1 + 2N + 1; if no\n#             arguments are equal, the last argument value is returned\n# arguments : 4 or more expressions, must be even # of arguments\n# returns   : the value of the argument at 1 + 2N + 1 if argument 1 + 2N\n#             is equal to argument1; else the last argument value\n#\n# example   : SELECT DECODE(somecolumn,\n#                    'first value', 'first value matched'\n#                    '2nd value', '2nd value matched'\n#                    'no value matched'\n#                    )\n\nINSERT\n# purpose   : string where L characters have been deleted from STR1, beginning at S,\n#             and where STR2 has been inserted into STR1, beginning at S.  NULL for any NULL args.\n# arguments : STR1, S, L, STR2\n\nHEX & OCT & BIN\n# purpose   : convert number X from decimal to hex/octal/binary; equiv. to CONV(X, 10, 16/8/2)\n# arguments : X\n\nLEFT & RIGHT\n# purpose   : leftmost or rightmost L characters in STR, or NULL for any NULL args\n# arguments : STR1, L\n\nLOCATE aka POSITION\n# purpose   : starting position (one-based) of the first occurrence of STR1\nwithin STR2; 0 if it doesn't occur and NULL for any NULL args\n# arguments : STR1, STR2, and an optional S (starting position to search)\n\nLOWER & UPPER aka LCASE & UCASE\n# purpose   : lower-case or upper-case a string\n# arguments : a string\n# returns   : the sting lower or upper cased\n\nLTRIM & RTRIM\n# purpose   : left/right counterparts for TRIM\n# arguments : string\n\nOCTETLENGTH\n# purpose   : length of the string in bytes (not characters)\n# arguments : string\n\nREGEX\n# purpose   : test if a string matches a perl regular expression\n# arguments : a string and a regex to match the string against\n# returns   : boolean value of the regex match\n#\n# example   : ... WHERE REGEX(col3,'/^fun/i') ... matches rows\n#             in which col3 starts with \"fun\", ignoring case\n\nREPEAT\n# purpose   : string composed of STR1 repeated C times, or NULL for any NULL args\n# arguments : STR1, C\n\nREPLACE aka SUBSTITUTE\n# purpose   : perform perl subsitution on input string\n# arguments : a string and a substitute pattern string\n# returns   : the result of the substitute operation\n#\n# example   : ... WHERE REPLACE(col3,'s/fun(\\w+)nier/$1/ig') ... replaces\n#                      all instances of /fun(\\w+)nier/ in col3 with the string\n#                      between 'fun' and 'nier'\n\nSOUNDEX\n# purpose   : test if two strings have matching soundex codes\n# arguments : two strings\n# returns   : true if the strings share the same soundex code\n#\n# example   : ... WHERE SOUNDEX(col3,'fun') ... matches rows\n#             in which col3 is a soundex match for \"fun\"\n\nSPACE\n# purpose   : a string of spaces\n# arguments : number of spaces\n\nSUBSTRING\nSUBSTRING( string FROM startpos [FOR length] )\n\nReturns the substring starting at startpos and extending for \"length\" character or until the\nend of the string, if no \"length\" is supplied. Examples:\n\nSUBSTRING( 'foobar' FROM 4 )       # returns \"bar\"\n\nSUBSTRING( 'foobar' FROM 4 FOR 2)  # returns \"ba\"\n\nNote: The SUBSTRING function is implemented in SQL::Parser and SQL::Statement and, at the\ncurrent time, can not be over-ridden.\n\nSUBSTR\n# purpose   : same as SUBSTRING, except with comma-delimited params, instead of\nwords (NULL for any NULL args)\n# arguments : string, startpos, [length]\n\nTRANSLATE\n# purpose   : transliteration; replace a set of characters in a string with another\nset of characters (a la tr///), or NULL for any NULL args\n# arguments : string, string to replace, replacement string\n\nTRIM\nTRIM ( [ [LEADING|TRAILING|BOTH] ['trimchar'] FROM ] string )\n\nRemoves all occurrences of <trimchar> from the front, back, or both sides of a string.\n\nBOTH is the default if neither LEADING nor TRAILING is specified.\n\nSpace is the default if no trimchar is specified.\n\nExamples:\n\nTRIM( string )\ntrims leading and trailing spaces from string\n\nTRIM( LEADING FROM str )\ntrims leading spaces from string\n\nTRIM( 'x' FROM str )\ntrims leading and trailing x's from string\n\nNote: The TRIM function is implemented in SQL::Parser and SQL::Statement and, at the current\ntime, can not be over-ridden.\n\nUNHEX\n# purpose   : convert each pair of hexadecimal digits to a byte (or a Unicode character)\n# arguments : string of hex digits, with an optional encoding name of the data string\n"
                },
                {
                    "name": "Numeric Functions",
                    "content": "ABS\n# purpose   : find the absolute value of a given numeric expression\n# arguments : numeric expression\n\nCEILING (aka CEIL) & FLOOR\n# purpose   : rounds up/down to the nearest integer\n# arguments : numeric expression\n\nEXP\n# purpose   : raise e to the power of a number\n# arguments : numeric expression\n\nLOG\n# purpose   : base B logarithm of X\n# arguments : B, X or just one argument of X for base 10\n\nLN & LOG10\n# purpose   : natural logarithm (base e) or base 10 of X\n# arguments : numeric expression\n\nMOD\n# purpose   : modulus, or remainder, left over from dividing X / Y\n# arguments : X, Y\n\nPOWER aka POW\n# purpose   : X to the power of Y\n# arguments : X, Y\n\nRAND\n# purpose   : random fractional number greater than or equal to 0 and less than the value of X\n# arguments : X (with optional seed value of Y)\n\nROUND\n# purpose   : round X with Y number of decimal digits (precision)\n# arguments : X, optional Y defaults to 0\n\nSIGN\n# purpose   : returns -1, 0, 1, NULL for negative, 0, positive, NULL values, respectively\n# arguments : numeric expression\n\nSQRT\n# purpose   : square root of X\n# arguments : X\n\nTRUNCATE aka TRUNC\n# purpose   : similar to ROUND, but removes the decimal\n# arguments : X, optional Y defaults to 0\n"
                },
                {
                    "name": "Trigonometric Functions",
                    "content": "All of these functions work exactly like their counterparts in Math::Trig; go there for\ndocumentation.\n\nACOS\nACOSEC\nACOSECH\nACOSH\nACOT\nACOTAN\nACOTANH\nACOTH\nACSC\nACSCH\nASEC\nASECH\nASIN\nASINH\nATAN\nATANH\nCOS\nCOSEC\nCOSECH\nCOSH\nCOT\nCOTAN\nCOTANH\nCOTH\nCSC\nCSCH\nSEC\nSECH\nSIN\nSINH\nTAN\nTANH\nTakes a single parameter. All of Math::Trig's aliases are included.\n\nATAN2\nThe y,x version of arc tangent.\n\nDEG2DEG\nDEG2GRAD\nDEG2RAD\nConverts out-of-bounds values into its correct range.\n\nGRAD2DEG\nGRAD2GRAD\nGRAD2RAD\nRAD2DEG\nRAD2GRAD\nRAD2RAD\nLike their Math::Trig's counterparts, accepts an optional 2nd boolean parameter (like TRUE)\nto keep prevent range wrapping.\n\nDEGREES\nRADIANS\nDEGREES and RADIANS are included for SQL-92 compatibility, and map to RAD2DEG and DEG2RAD,\nrespectively.\n\nPI  PI can be used without parentheses.\n"
                },
                {
                    "name": "System Functions",
                    "content": "DBNAME & USERNAME (aka USER)\n# purpose   : name of the database / username\n# arguments : none\n"
                },
                {
                    "name": "Special Utility Functions",
                    "content": "IMPORT\nCREATE TABLE foo AS IMPORT(?)    ,{},$externalexecutedsth\nCREATE TABLE foo AS IMPORT(?)    ,{},$AoA\n\nRUN\nTakes the name of a file containing SQL statements and runs the statements; see SQL::Parser for\ndocumentation.\n"
                }
            ]
        },
        "Submitting built-in functions": {
            "content": "If you make a generally useful UDF, why not submit it to me and have it (and your name) included\nwith the built-in functions? Please follow the format shown in the module including a\ndescription of the arguments and return values for the function as well as an example. Send them\nto the dbi-dev@perl.org mailing list (see <http://dbi.perl.org>).\n\nThanks in advance :-).\n",
            "subsections": []
        },
        "ACKNOWLEDGEMENTS": {
            "content": "Dean Arnold supplied DECODE, COALESCE, REPLACE, many thanks! Brendan Byrd added in the\nNumeric/Trig/System functions and filled in the SQL92/ODBC gaps for the date/string functions.\n\nAUTHOR & COPYRIGHT\nCopyright (c) 2005 by Jeff Zucker: jzuckerATcpan.org Copyright (c) 2009-2020 by Jens Rehsack:\nrehsackATcpan.org\n\nAll rights reserved.\n\nThe module may be freely distributed under the same terms as Perl itself using either the \"GPL\nLicense\" or the \"Artistic License\" as specified in the Perl README file.\n",
            "subsections": []
        }
    },
    "summary": "SQL::Statement::Functions - built-in & user-defined SQL functions",
    "flags": [],
    "examples": [],
    "see_also": []
}