<<

. 121
( 132 .)



>>

RETURNS: string

It returns a string representation of the number N, converted from base
from_base to base to_base. It returns NULL if any argument is NULL. The argument
N is interpreted as an integer, but may be specified as an integer or as a string. The
minimum base is 2, and the maximum base is 36. If to_base is a negative number,
N is regarded as a signed number; otherwise N is treated as unsigned. CONV works
with 64-bit precision.

mysql> select conv(3,10,2);
+--------------+
| conv(3,10,2) |
+--------------+
| 11 |
+--------------+


BIN
This function returns the value of N as a binary (base-2) number. BIN treats N as a
64-bit signed integer value. Any decimal remainder is discarded. Negative numbers
consist of 64 bits. Positive numbers have leading zeroes discarded. It returns NULL
if N is NULL.

BIN(N)
RETURNS: string

mysql> select bin(3);
+--------+
| bin(3) |
+--------+
| 11 |
+--------+


OCT
This function returns a string representation of the octal value of N, where N is a long
(BIGINT) number. It is equivalent to CONV(N,10,8). It returns NULL if N is NULL.

OCT(N)
RETURNS: string
Appendix J: MySQL Function Reference 711


HEX
This function returns a string representation of the hexadecimal value of N, where N
is a long (BIGINT) number. This is equivalent to CONV(N,10,16). It returns NULL if
N is NULL.

HEX(N)
RETURNS: string

mysql> select hex(1000);
+-----------+
| hex(1000) |
+-----------+
| 3E8 |
+-----------+
1 row in set (0.00 sec)


CHAR
This function interprets the arguments as integers and returns a string consisting of
the ASCII-code values of those integers. NULL values are skipped.

CHAR(N,...)
RETURNS: string

mysql> select char(74,65,89);
+----------------+
| char(74,65,89) |
+----------------+
| JAY |
+----------------+
1 row in set (0.00 sec)


CONCAT
This function returns the string that results from the concatenation of the argu-
ments. It returns NULL if any argument is NULL. CONCAT may have more than two
arguments. A numeric argument is converted to the equivalent string form.

CONCAT(str1,str2,...) (used in examples)
RETURNS: string
712 Part V: Appendixes

This function is used in the following example to prepend a wildcard character
onto the column in the WHERE clause of a query:

select 1 from blocked_domains
WHERE ˜$REMOTE_HOST™ like concat(˜%™,domain)
and release_dt is null


LENGTH
This function returns the length of the string str. If a numeric value is used as the
argument, it™s converted to a string first. Note that for CHAR_LENGTH() multi-byte
characters are counted only once.

LENGTH(mixed)

or

CHAR_LENGTH(mixed)

RETURNS: int
mysql> select length(˜mysql functions™);
+---------------------------+
| length(˜mysql functions™) |
+---------------------------+
| 15 |
+---------------------------+
1 row in set (0.00 sec)


LOCATE
This function returns the position of the first occurrence of substring substr in
string str. Returns 0 if substr is not in str.

LOCATE(substr,str [,pos])

or

POSITION(substr IN str)
RETURNS: int

The optional third argument enables you to specify an offset at which to start
the search:

mysql> select locate(˜s™, ˜mysql functions™) as example1,
-> locate(˜s™, ˜mysql functions™,4) as example2;
+----------+----------+
Appendix J: MySQL Function Reference 713

| example1 | example2 |
+----------+----------+
| 3| 15 |
+----------+----------+
1 row in set (0.00 sec)


INSTR
This function returns the position of the first occurrence of substring substr in
string str. It is the same as LOCATE(), except that the arguments are swapped and
no argument that indicates position is allowed.

INSTR(str,substr)
RETURNS: int


LPAD
This function returns the string str, left-padded with the string padstr until str is
len characters long.

LPAD(str,len,padstr)
RETURNS: string

mysql> select lpad(˜foo™, 15, ˜k™);
+----------------------+
| lpad(˜foo™, 15, ˜k™) |
+----------------------+
| kkkkkkkkkkkkfoo |
+----------------------+
1 row in set (0.00 sec)


RPAD
This function returns the string str, right-padded with the string padstr until str
is len characters long.

RPAD(str,len,padstr)
RETURNS: string


LEFT
This function returns the leftmost len characters from the string str:

LEFT(str,len)
RETURNS: string
714 Part V: Appendixes

mysql> select left(˜mysql functions™, 10);
+-----------------------------+
| left(˜mysql functions™, 10) |
+-----------------------------+
| mysql func |
+-----------------------------+
1 row in set (0.02 sec)


RIGHT
This function returns the rightmost len characters from the string str:

RIGHT(str,len)
RETURNS: string


SUBSTRING
This function returns a substring len characters long from string str, starting at
position pos and continuing for len number of characters. The variant form that
uses FROM is ANSI SQL92 syntax.

SUBSTRING(str,pos[,len])

or

SUBSTRING(str FROM pos FOR len)

or

MID(str,pos,len) (used in examples)
RETURNS: string

mysql> select mid(˜mysqlfunctions™,6,8);
+---------------------------+
| mid(˜mysqlfunctions™,6,8) |
+---------------------------+
| function |
+---------------------------+
1 row in set (0.00 sec)


SUBSTRING_INDEX
This function returns the substring from string str after count occurrences of the
delimiter delim. If count is positive, everything to the left of the final delimiter
(counting from the left) is returned; if count is negative, everything to the right of
Appendix J: MySQL Function Reference 715

the final delimiter (counting from the right) is returned. If count is 0, nothing is
returned.

SUBSTRING_INDEX(str,delim,count) (used in examples)
RETURNS: string

mysql> select substring_index(˜mysqlfunctionsmysql™, ˜fu™, 1);
+-------------------------------------------------+
| substring_index(˜mysqlfunctions™, ˜fu™, 1) |
+-------------------------------------------------+
| mysql |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index(˜mysqlfunctionsmysql™, ˜fu™, -1);
+--------------------------------------------------+
| substring_index(˜mysqlfunctionsmysql™, ˜fu™, -1) |
+--------------------------------------------------+
| nctionsmysql |
+--------------------------------------------------+
1 row in set (0.00 sec)


LTRIM
This function returns the string str with leading spaces (and only spaces ” no other
whitespace characters) removed:

LTRIM(str)
RETURNS: string


RTRIM
This function returns the string str with trailing-space characters removed:

RTRIM(str)
RETURNS: string


TRIM
This function returns the string str with all remstr prefixes and/or suffixes
removed. If none of the specifiers BOTH, LEADING, and TRAILING is given, BOTH is
assumed. If remstr is not specified, spaces are removed.

<<

. 121
( 132 .)



>>