MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
Character String Functions
Describes some commonly used character string functions like, CONCAT(), CHAR_LENGTH(), SUBSTRING(), UPPER(), LOWER(), TRIM(), and POSITION().
MySQL supports a number of built-in functions that allows you to manipulate character strings.
"CONCAT(str_1, str_2)" - Returns a character string made by joining the specified two strings. This is a replacement of the concatenation operator, "||", defined in SQL-92.
"CHAR_LENGTH(string)" - Returns a number indicating the number of characters of the specified string.
"BIT_LENGTH(string)" - Returns a number indicating the number of bits of the specified string.
"SUBSTRING(string, FROM position FOR length)" - Returns a character string made by extracting a portion of the specified string according to the specified position and the specified length.
"UPPER(string)" - Returns a character string made by converting the specified string to upper case characters.
"LOWER(string)" - Returns a character string made by converting the specified string to lower case characters.
"TRIM(LEADING|TRAILING|BOTH char FROM string)" - Returns a character string made by removing leading (trailing or both) padding characters from the specified string.
"POSITION(substr IN str)" - Returns a number indicating the position of a short string inside a long string.
Examples of character strings functions, CharStringFunctions.sql:
-- CharStringFunctions.sql -- Copyright (c) 1999 HerongYang.com. All Rights Reserved. -- SELECT CONCAT('abc','xyz'); -- MySQL does not support || SELECT UPPER('fortran'); SELECT SUBSTRING('Hello world!',7,5); -- Starting position is 1 SELECT TRIM(' 011.99000 '); -- Trimming ' ' on both ends SELECT TRIM(BOTH ' ' FROM ' 011.99000 '); SELECT TRIM(LEADING '0' FROM '011.99000'); SELECT TRIM(TRAILING '0' FROM '011.99000'); SELECT CHAR_LENGTH('Hello world!'); SELECT POSITION('world' IN 'Hello world!');
I got the the following output when running the above SQL script:
herong> %mysql%\bin\mysql --user=root --password=TopSecret \ < CharStringColumns.sql CONCAT('abc','xyz') abcxyz UPPER('fortran') FORTRAN SUBSTRING('Hello world!',7,5) world TRIM(' 011.99000 ') 011.99000 TRIM(BOTH ' ' FROM ' 011.99000 ') 011.99000 TRIM(LEADING '0' FROM '011.99000') 11.99000 TRIM(TRAILING '0' FROM '011.99000') 011.99 CHAR_LENGTH('Hello world!') 12 POSITION('world' IN 'Hello world!') 7
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux