SQL: String Functions

[chili5]

SQL Functions - String Functions

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:

65

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:

4

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:

Jame****here

Format Function

Returns a number formatted to a certain amount of decimals. The function prototype is:

FORMAT(num,D)

The number num is formatted in a format similar to '#,###.##'. The number of decimals is D values.

Example:

SELECT FORMAT(1234.343332,3)

Output:

1,234.343

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:

test

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:

Test
Test
Test
Test

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:

Test this is just a yo! yo! yo!

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:

tset

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:

est

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:

te

Uppercase Function

The UPPER function converts a string into upper case.

Example:

SELECT UPPER("test");

Output:

TEST

[Jordan]

Nice little tutorial. +rep!

[WingedPanther]

Very handy list of functions. I use them a LOT. +rep