SQL provides a large amount of functions for string manipulations.
ASCII
The ASCII function is used to return a numeric value for the leftmost character. Each character has a numeric value that represents it.
Example:
SELECT ASCII('A')
Output:
[QUOTE]
65
[/QUOTE]
65 is the numeric representation. An interesting property of characters is that each letter is one integer value away. This makes it great for adding and subtracting other values. You can perform math with characters.
So:
SELECT CHAR(ASCII('A')+1)
Returns B. This converts A to it's ascii value and adds 1. Then it converts it back to a character. This shows that the letters are exactly one integer value away.
Thus we can use this method to convert chars from upper to lowercase by observing patterns in an ASCII chart.
SELECT CHAR(ASCII('A')+ASCII('a')-ASCII('A'))
This code converts A to lowercase "a".
Character Length
The character length function returns the number of letters in the string.
SELECT CHARACTER_LENGTH("Hiii")
This function returns the number of characters in the string.
Output:
[QUOTE]
4
[/QUOTE]
Another method that does the same thing is CHAR_LENGTH.
CONCAT method
This method is used to join strings together.
SELECT CONCAT("James","Hi","There");
The function takes a variable number of arguments and joins them together.
The returning value is:
[QUOTE]
Jame****here
[/QUOTE]
Format Function
Returns a number formatted to a certain amount of decimals. The function prototype is:
[QUOTE]
FORMAT(num,D)
[/QUOTE]
The number num is formatted in a format similar to '#,###.##'. The number of decimals is D values.
Example:
SELECT FORMAT(1234.343332,3)
Output:
[QUOTE]
1,234.343
[/QUOTE]
Lowercase Functions
The function LCASE returns a string in lower case. This function does the same thing as the LOWER function.
Example:
SELECT LOWER("Test");
Output:
[QUOTE]
test
[/QUOTE]
Trim Functions
There are three functions:
- LTRIM
- RTRIM
- TRIM
that remove the space from around the string. The LTRIM function removes all leading spaces before the left side of the string. The RTRIM function removes all trailing spaces after the right side of the string. The TRIM function removes all leading and trailing spaces.
Examples:
SELECT LTRIM(" Test");
SELECT RTRIM("Test ");
SELECT TRIM(" Test ");
SELECT RTRIM(" Test ");
Output:
[QUOTE]
Test
Test
Test
Test
[/QUOTE]
Replace
The generic function is:
REPLACE(str,replace,new)
The replace function replaces all instances of a string replace with new.
Example:
SELECT REPLACE("Test this is just a test test test","test","yo!");
The output:
[QUOTE]
Test this is just a yo! yo! yo!
[/QUOTE]
All instances of "this" will be replaced with "yo". This method is case-sensitive. Notice, how Test was not replaced?
Reverse Function
This function returns a string that is reversed.
Example:
SELECT REVERSE("test");
The output is:
[QUOTE]
tset
[/QUOTE]
Substring Function
There are two versions of this function:
SUBSTR(str,pos)
and
SUBSTR(str,pos,len)
The first function copies all the characters from index pos to the end of the string. One thing to note, the index of the first character is one.
Example:
SELECT SUBSTR("test",2);
Output:
[QUOTE]
est
[/QUOTE]
The second version specifies how many characters to copy and where to start copying from.
Example:
SELECT SUBSTR("test",1,2);
This function starts at the first character and copies two characters.
Output:
[QUOTE]
te
[/QUOTE]
Uppercase Function
The UPPER function converts a string into upper case.
Example:
SELECT UPPER("test");
Output:
[QUOTE]
TEST
[/QUOTE]
