1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
Other String Functions
======================
LOAD_FILE()
-----------
Load the named file
ELT()
-----
Return string at index number
EXPORT_SET()
------------
Return a string
FORMAT()
--------
Return a number formatted to specified number of decimal places
LOAD_FILE()
-----------
Load the named file
LPAD()
-------
Return the string argument, left-padded with the specified string
MAKE_SET()
----------
Return a set of comma-separated strings that have the corresponding bit in bits set
MATCH()
-------
Perform full-text search
MID()
-----
Return a substring starting from the specified position
ORD()
-----
Return character code for leftmost character of the argument
QUOTE()
-------
Escape the argument for use in an SQL statement
REPEAT()
--------
Repeat a string the specified number of times
REPLACE()
---------
The REPLACE() function returns a string with all occurrences of the 'from_str' replaced by 'to_str'. REPLACE is case-sensitive when searching for 'from_str'.
Syntax:
REPLACE(str,from_str,to_str)
For example: ::
SELECT REPLACE('wwww.google.com', 'w', 'v');
Returns: vvv.google.com
REVERSE()
---------
This function returns a string argument with the characters in reverse order. ::
SELECT REVERSE('abcd');
Returns: dcba
RIGHT()
-------
Return the specified rightmost number of characters
RPAD()
------
Append string the specified number of times
SOUNDEX()
---------
Return a soundex string
SUBSTRING()
-----------
Returns the substring as specified
Examples that use SUBSTRING() in the SELECT clause:
The SUBSTRING() function is used to extract a character string (using a given starting position and a given length). ::
SELECT
SUBSTRING(course_designater,6,3) as 'Course number'
FROM Courses
WHERE course_designater LIKE 'Excel%'
LIMIT 10;
You can also format a column using SUBSTRING() in combination with functions like LOWER() and UPPER(). ::
SELECT
CONCAT(UPPER(SUBSTRING(lastname,1,1)),
LOWER(SUBSTRING(lastname,2,29)))
FROM Students
LIMIT 10;
|