dcsimg
 

A Beginner’s Guide to SQL String Functions

Thursday Nov 21st 2019 by Hannes Du Preez

Get an introduction to many of the many ways you can manipulate and interact with strings using SQL. Read on for more!

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.

# # #

Home
Mobile Site | Full Site