<<

. 122
( 132 .)



>>

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) (used in
examples)
RETURNS: string
716 Part V: Appendixes

mysql> select trim(both ˜\n™ from ˜\n mystring™);
+------------------------------------+
| trim(both ˜\n™ from ˜\n mystring™) |
+------------------------------------+
| mystring |
+------------------------------------+
1 row in set (0.00 sec)

Note that remstr will exactly match only the exact sequence of characters. So
putting \t\n\ in the remstr argument in the preceding example removes only
occurrences where tabs and newlines appear consecutively.

REPLACE
This function returns the string str with all occurrences of the string from_str
replaced by the string to_str:

REPLACE(str,from_str,to_str)
RETURNS: string


SOUNDEX
This function returns a soundex string from str:

SOUNDEX(str)
RETURNS: string

Two strings that sound “about the same” in English should have identical
soundex strings. A “standard” soundex string is four characters long, but the
SOUNDEX() function returns a string of arbitrary length. You can use SUBSTRING()
on the result to get a “standard” soundex string. All non-alphanumeric characters
are ignored in the given string. All international alpha characters outside the A“Z
range are treated as vowels.

mysql> select soundex(˜functions™), soundex(˜junctions™),
soundex(˜fiction™);
+----------------------+----------------------+--------------------+
| soundex(˜functions™) | soundex(˜junctions™) | soundex(˜fiction™) |
+----------------------+----------------------+--------------------+
| F52352 | J52352 | F235 |
+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
Appendix J: MySQL Function Reference 717


SPACE
This function returns a string consisting of N space characters:

SPACE(N)
RETURNS: string


REPEAT
This function returns a string consisting of the string str repeated count times. If
count is less than or equal to 0, it returns an empty string. It returns NULL if str or
count are NULL.

REPEAT(str,count)
RETURNS: string

mysql> select repeat(˜foo™, 10);
+--------------------------------+
| repeat(˜foo™, 10) |
+--------------------------------+
| foofoofoofoofoofoofoofoofoofoo |
+--------------------------------+


REVERSE
This function returns the string str with the order of the characters reversed:

REVERSE(str)
RETURNS: string


INSERT
This function returns the string str, with the substring len characters long begin-
ning at position pos replaced by the string newstr:

INSERT(str,pos,len,newstr)
RETURNS: string

mysql> select insert(˜mysqlfunctions™, 6,2,™FU™);
+------------------------------------+
| insert(˜mysqlfunctions™, 6,2,™FU™) |
+------------------------------------+
| mysqlFUnctions |
+------------------------------------+
1 row in set (0.44 sec)
718 Part V: Appendixes


ELT
This function returns str1 if N equals 1, str2 if N equals 2, and so on. It returns
NULL if N is less than 1 or greater than the number of arguments. ELT() is the com-
plement of FIELD().

ELT(N,str1,str2,str3,...)
RETURNS: string

mysql> select elt(2, ˜foo™, ˜bar™, ˜foobar™);
+--------------------------------+
| elt(2, ˜foo™, ˜bar™, ˜foobar™) |
+--------------------------------+
| bar |
+--------------------------------+
1 row in set (0.00 sec)


FIELD
This function returns the index of str in the str1, str2, str3, ... list. It
returns 0 if str is not found. FIELD() is the complement of ELT().

FIELD(str,str1,str2,str3,...)
RETURNS: int

mysql> select field(˜foobar™, ˜foo™, ˜bar™, ˜foobar™);
+-----------------------------------------+
| field(˜foobar™, ˜foo™, ˜bar™, ˜foobar™) |
+-----------------------------------------+
| 3|
+-----------------------------------------+
1 row in set (0.01 sec)


LCASE
This function returns the string str with all characters changed to lower case
according to the current character-set mapping (the default is ISO-8859-1 Latin1):

LCASE(str) or LOWER(str) (used in examples)
RETURNS: string
Appendix J: MySQL Function Reference 719


UCASE
This function returns the string str with all characters changed to upper case
according to the current character-set mapping (the default is ISO-8859-1 Latin1):

UCASE(str) or UPPER(str)
RETURNS: string




Date and Time Functions
MySQL offers many functions for calculating dates. Of all the MySQL functions
available, these are the ones you will probably use most frequently.
The DATE_FORMAT function enables you to format dates to take the form of
MySQL timestamps. In addition, several functions will enable you to get specific
date information from a column with ease. For example, to find the day of the week
of all the entries in a timestamp column, you could use the following code:

mysql> select dayname(created) from guestbook;
+------------------+
| dayname(created) |
+------------------+
| Sunday |
| Sunday |
| Wednesday |
| Sunday |
| Sunday |
| Wednesday |
| Wednesday |
| Wednesday |
+------------------+


DAYOFWEEK
This function returns the weekday index for date (1 for Sunday, 2 for Monday, and
so on up to 7 for Saturday). These index values correspond to the ODBC standard.
If an invalid date is supplied to the date functions, they return null.

DAYOFWEEK(date) (used in examples)
RETURNS: int
mysql> select dayofweek(˜2001-01-01™);
+-------------------------+
| dayofweek(˜2001-01-01™) |
720 Part V: Appendixes

+-------------------------+
| 2|
+-------------------------+
1 row in set (0.33 sec)


WEEKDAY
This function returns the weekday index for date (0 for Monday, 1 for Tuesday, and
so on up to 6 for Sunday):

WEEKDAY(date) (used in examples)
RETURNS: int


DAYOFMONTH
This function returns the day of the month for date, in the range of 1 to 31:

DAYOFMONTH(date)
RETURNS: int


DAYOFYEAR
This function returns the day of the year for date, in the range of 1 to 366:

DAYOFYEAR(date)
RETURNS: int

mysql> select dayofmonth(˜02-01-2000™);
+--------------------------+
| dayofmonth(˜02-01-2000™) |
+--------------------------+
| 20 |
+--------------------------+
1 row in set (0.00 sec)


MONTH
This function returns the month for date, in the range of 1 to 12:

MONTH(date)
RETURNS: int
Appendix J: MySQL Function Reference 721


DAYNAME
This function returns the full name of the weekday for date:

DAYNAME(date)
RETURNS: string

mysql> select dayname(˜10/01/2000™);
+-----------------------+
| dayname(˜10/01/2000™) |
+-----------------------+
| Wednesday |
+-----------------------+
1 row in set (0.00 sec)


MONTHNAME
This function returns the full English-language name of the month for date:

MONTHNAME(date)
RETURNS: string


QUARTER
This function returns the quarter of the year for date, in the range of 1 to 4:

QUARTER(date)
RETURNS: int

To find all the people who signed your guestbook in the second quarter of the
year, you could use the following command:

select name from guestbook WHERE quarter(created) = 2;


WEEK
With a single argument, this function returns the week for date, in the range of 0
to 53:

WEEK(date [, first])
RETURNS: int

The optional second argument enables you to specify whether the week starts on
Sunday or Monday. The week starts on Sunday if the second argument is 0 and on

<<

. 122
( 132 .)



>>