There are quite a few string functions in the SQL language, and today I will present most of them. To be more specific, SQL has the following string functions:
SQL has the following string functions:
- ASCII
- CHAR
- CHARINDEX
- CONCAT
- CONCAT_WS
- DATALENGTH
- DIFFERENCE
- FORMAT
- LEFT
- LEN
- LOWER
- LTRIM
- NCHAR
- PATINDEX
- QUOTENAME
- REPLACE
- REPLICATE
- REVERSE
- RIGHT
- RTRIM
- SOUNDEX
- SPACE
- STR
- STUFF
- SUBSTRING
- TRANSLATE
- TRIM
- UNICODE
- UPPER
To help you know what each of these does, I’ll present them one by one:
ASCII
The ASCII SQL function returns the ASCII (American Standard Code for Information Interchange) value of the first character in a field.
SELECT ASCII(StudentName) FROM Students
This example will produce the number 72 if the StudentName field contained the string “Hannes”. Why 72? 72 is the ASCII code for H.
CHAR
The CHAR SQL function is the opposite of the ASCII function in that it returns the character based on n ASCII number.
SELECT CHAR(72)
In this example, the number 72 is passed to CHAR(). This will return the character H.
CHARINDEX
The CHARINDEX SQL function returns the index of a character in a certain string.
SELECT CHARINDEX('H', 'Hannes') --Returns 1
This example searches for the character ‘H’, and returns the index value for its location. This will result in 1. If the searched were changed to look for the character ‘s’, it would returned 6.
CONCAT
The CONCAT SQL function adds two strings together.
SELECT CONCAT('Hannes', ' du Preez') --Returns Hannes du Preez
This example joins the two strings ‘Hannes’ and ‘ du Preez’ together, producing a single string ‘Hannes du Preez’.
DATALENGTH
The DATALENGTH SQL function returns the length of an expression.
SELECT DATALENGTH('Hannes') --Returns 6
This example shows the use of DATALENGTH in action. It determines the length of the string ‘Hannes’ and returns the result, which is 6.
DIFFERENCE
The DIFFERENCE SQL function compares two SOUNDEX values, an example follows. More on SOUDNEX a bit later. The DIFFERENCE SQL function returns an integer between 1 and 4. 1 being the weakest, 4 being the strongest. The following example returns 2, which indicates that there is some similarity, but for the most part these are different sounding words.
SELECT DIFFERENCE('Door', 'Floor') --Returns 2
FORMAT
The FORMAT SQL function formats a value with the specified format.
SELECT FORMAT(123456789, '#-###-####') --Returns 1-234-5789
This example formats a group of numbers to look like a standard telephone number in the United States.
LEFT
The LEFT SQL function extracts a specified amount of characters from a string.
SELECT LEFT('du Preez', 5) --Returns du Pr
In this example you take 5 characters from the left position. Note: this also includes spaces which are considered characters.
LOWER
The LOWER SQL function converts a string to lowercase, an Example follows:
SELECT LOWER('Hannes du Preez')
This example would simply force everything to lower case, resulting in ‘hannes du preez’.
LTRIM
The LTRIM SQL function removes leading spaces from a specified string. In the next example, you will see that there is a bunch of spaces in front of the actual string. LTRIM removes all of them and returns the string.
SELECT LTRIM(' Password') --Returns 'Password'
In this example, you can see that there is a bunch of spaces in front of the actual string. LTRIM removes all of them and returns the string.
QUOTENAME
The LTRIM SQL function returns string with delimiters added. It adds brackets, depending on which bracket type you supply. Any of these are allowed:
- ‘
- []
- “
- ()
- ><
- {}
- `
SELECT QUOTENAME('Hannes', '''') --Returns 'Hannes'
This example shows how to add single quote marks to the string.
REPLACE
The REPLACE SQL function replaces occurrences within a string with a new string.
SELECT REPLACE('Hannes', 'n', 'd') --Returns 'Haddes'
In the following example all the occurrences of the character ‘n’ are replaced with the character ‘d’.
REPLICATE
The REPLACE SQL function repeats a string. Shown next, the string ‘databasejournal.com ’ is replaced three times.
SELECT REPLICATE('databasejournal.com ', 3) --Returns databasejournal.com databasejournal.com databasejournal.com
REVERSE
The REVERSE SQL function reverses a string, as shown next:
SELECT REVERSE('Hannes du Preez') --Returns zeerP ud sennaH
As you can see in this example, the characters are simply reversed.
RIGHT
Like the LEFT SQL function, the RIGHT SQL function also extracts a specified amount of characters from a string but starts from right.
SELECT RIGHT('du Preez', 5) --Returns Preez
This example returns the 5 rightmost characters of the string that is received.
RTRIM
Like the LTRIM SQL function, the RTRIM SQL function removes spaces from a specified string, but from the back. These are known as trailing spaces. The following example shows a string ending with many spaces, RTRIM gets rid of them.
SELECT RTRIM('Password ') --Returns 'Password'
SOUNDEX
The SOUNDEX SQL function returns a four-character code which to evaluate the similarity of how two expressions sound.
SELECT SOUNDEX('Floor'), SOUNDEX('Door')
This example will return F460 for ‘Floor’ and D600 for ‘Door’. These two values can then be compared to determine if the words sound alike.
STR
The STR SQL function converts a number to a string.
SELECT STR(150) --Returns 150, but as a string
In this example, the number 150 is supplied, but with the help of the STR function it is converted to a string.
STUFF
The STUFF SQL function deletes a part of a string and then inserts another part into the deleted string’s place, at a specified position.
SELECT STUFF('Hannes du Preez', 1, 6, 'Ockert') --Returns Ockert du Preez
In this example, a starting string of my name is used (‘Hannes du Preez’). Starting in position 1 of this starting string (the second parameter), the next 6 characters (the third value passed to STUFF) are replaced with the string ‘Ockert’. The result is that ‘Hannes’ is replaced with ‘Ockert’.
SUBSTRING
The SUBSTRING SQL function extracts some characters from a string.
SELECT SUBSTRING('Hannes du Preez', 8, 2) --Returns du
This example extracts the substring ‘du’. You supply the position where the extraction should start (the second parameter to SUBSTRING), along with the number of characters it should take (the third parameter).
TRIM
The TRIM SQL function removes leading and trailing spaces from a string. The following example contains lots of spaces in front and back of the string, TRIM removes them all.
SELECT TRIM(' Hannes du Preez ') --Returns Hannes du Preez
UPPER
The UPPER SQL function converts a string to uppercase. The following example changes all the letters in my name to uppercase.
SELECT UPPER('Hannes du Preez') -–HANNES DU PREEZ
Conclusion
This was a quick overview of the most common string SQL String functions. As you work with SQL, you will find many of these to be useful. As a note, while most of the examples used hardcoded values, these will generally be used with variables as you select data from various sources.
# # #
