The built in SQL String
functions make it possible for you to find and alter text values, such as VARCHAR
and CHAR
datatypes, in SQLServer. Using these functions, you can alter a text value such as changing “Smith, Joe
” to “Joe Smith
.”
If you are not familiar with SQL functions, then I would recommend starting with the Introduction to SQL Server Built-In Functions.
To get the most of this and our other lessons, be sure to practice using the examples!
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012
database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
Introduction to SQL Server’s String Functions
The t-SQL string
functions are used to manipulate or return information about text expression such as CHAR
and VARCHAR datatypes.
There are many string
functions within SQL at your disposal. I would generally categorize them as:
- Position
- Transformation
- Character Set
- Soundex
All of the functions are listed on the String Functions (Transact-SQL) page. I would recommend visiting that page to learn about each function.
Rather than reiterate that material, we’ll focus on the functions I’ve seen in commonly used in business.
In the following tables, I categorized the functions and color coded them. The color code corresponds to the likelihood you would use that particular function in a business environment. Green are most likely to be used, and red less.
This isn’t a strict scale, and all functions have a use in some business case, but I wanted a way to help you winnow down the field to those most relevant.
Here is my attempt:
SQL Server String Functions – Commonly Used Functions in Green
Functions Used to Find Position
CHARINDEX
The CHARINDEX
function is used to find the position of one string
within another. This can be handy when you need to break data apart by dashes or commas.
The general form for the CHARINDEX
function is:
CHARRINDEX(value to find, value to search)
Where value to find is one or more characters that you wish to find in the value to search.
If the value is found, then the starting positon is returned. If the value isn’t found, then 0 (zero) is returned.
If either the value to find, or value to search are NULL
, then NULL
is returned.
CHARINDEX in action!
LEN
The LEN
function returns the number of characters in a string
.
In the following example, you can see that the length of “SQL Server” is 10
.
Example of the LEN function
Length is rarely used on its own. It is used mainly in conjunction with other functions, such as LEFT
and RIGHT
.
Functions Used to Transform Strings
LEFT and RIGHT
The LEFT
and RIGHT
functions are used to return either the beginning or ending portion of a string
.
LEFT
will return the beginning characters; whereas, RIGHT
is used to return the ending characters.
The general form for the LEFT
function is:
LEFT(value, length)
Where value
is the string
you’re working with, and length
is the number of characters you wish to return from the beginning of value.
Example of LEFT Function
If the length
is greater than the number of characters in the value
, then the entire string
is returned.
IF length
is negative, an error is thrown.
If either length
or value
is NULL
, then NULL
is returned.
The RIGHT string
function works much like LEFT
, but it returns the ending characters as opposed to the beginning.
SELECT RIGHT(‘SQL Rocks!’,6)
Returns the value ‘Rocks!’
Suppose the Production Manager wants a distinct list of all product number prefixes. How could you return that list?
If you look at the Production.Product ProductNumber
column value, you’ll notice the prefix is the first two characters. So knowing what we do now about the LEFT
function, the solution is a snap!
Here is what you can write:
SELECT DISTINCT LEFT(ProductNumber,2)
FROM Production.Product
The LEFT
function returns the first two characters. Since we’re using the DISTINCT
operator, duplicate results are eliminated from the result.
OK, so now the production manager is getting crazy! The last character of the ProductNumber
prefix means a lot to her. She wants to know what those distinct values are. What can we do to help?
We can use LEFT
and RIGHT
in conjunction. We already know how to get the LEFT
most characters, now all we need to do is take the last character from that result and return it as shown below.
Nesting Function to return characters
Here is the query you can use:
SELECT DISTINCT RIGHT(LEFT(ProductNumber,2),1)
FROM Production.Product
Like other functions, you can nest string
functions. Just remember that the results of one function can be used in another. You should read these expressions from the “inside out.”
SUBSTRING
The SUBSTRING
function is used to return characters from within another string
.
The general form of the SUBSTRING
function is:
SUBSTRING(value, position, length)
Where value
is the string
you’re working with, position
is character to start returning characters, and length
is the number of characters to return.
If length
is negative, an error is returned.
If either position
or length
are NULL
, then NULL
is returned.
SUBSTRING Example
For example:
SUBSTRING(‘SQL Server’,5,3)
returns ‘Ser
’. It the sequence of three characters starting in the fifth position.
SUBSTRING is a generalized form of the LEFT and RIGHT functions.
Extra Credit – Show how LEFT and RIGHT can be used together to simulate SUBSTRING. Show your answer in the comments below.
LEFT(‘SQL Server’,4)
and SUBSTRING(‘SQL Server’,1,4)
both return the beginning four characters.
RIGHT
is a little more complicated!
RIGHT(‘SQL Server’,4)
can be written as SUBSTRING(‘SQL Server’,7,4)
Which says to start at the 7th position and then return the next four characters. The general form for this, since you won’t always know the length of the value, is:
SUBSTRING(‘SQL Server’, LEN(‘SQL Server’),4)
Here is an example using SQL columns.
SELECT LEFT(Name,2) as Left2,
SUBSTRING(Name, 1, 2) as Substring2,
RIGHT(Name,3) as Right3,
SUBSTRING(Name, LEN(Name)-2,3) as Substring3
FROM Production.Product
Whose results are:
Results showing SUBSTRING same as LEFT and RIGHT
UPPER and LOWER
The UPPER
and LOWER string
functions are used to return values whose characters are in all upper or lower case respectively.
The general form for the UPPER
function is:
UPPER(value)
where value
is the string
you’re working with.
If value is NULL
, then NULL
is returned.
The form and behavior for LOWER
is similar.
Here is an example query:
SELECT FirstName + ' ' + LastName as FullName,
UPPER( FirstName + ' ' + LastName) as UpperName,
LOWER( FirstName + ' ' + LastName) as LowerName
FROM Person.Person
Which returns:
Examples using UPPER and LOWER
REPLACE
The REPLACE
function is good when you wish to find one or more characters in a string
and replace them with other characters. A common application is to replace all dashes ‘-‘ with spaces.
Here is the general form of the REPLACE
function:
REPLACE (value, pattern, replacement)
Where value
is the string
to work with, pattern
is the portion of the string
you wish to find and replace, and replacement
is the value to replace the pattern.
If any of the parameters are NULL
, then REPLACE
returns NULL
.
If pattern
isn’t found, nothing is replaced, and value
is returned in its original form.
In business, you’ll come across data from two separate systems, such as your systems and a customer or supplier’s systems where data is treated differently. This can cause issues, especially when it comes to matching.
Once common issue I’ve seen occurs with part numbers. Consider Adventure Works. Within the company, part numbers are formatted with dashes, such as ‘AR-5381
’; however, some suppliers have replaced the dashes with spaces like so ‘AR 5381
’.
Before we do a large scale data match, the production team wishes to provide the suppliers with our parts list with the dashes replaced with spaces.
Here is a query we could run to do so:
SELECT Name,
ProductNumber,
REPLACE(ProductNumber,'-',' ') as SupplierProductNumber
FROM Production.Product
And the result we can provide to the suppliers is:
Though you can’t have blank pattern, and if you think about it, that wouldn’t’ make too much sense, you can replace a pattern with a blank value. Why? Well, a good reason is to strip characters out of a string
.
In the ProductNumber
example, there may be cases where we want to use the product number without dashes. This query could be used in that case:
SELECT Name,
ProductNumber,
REPLACE(ProductNumber,'-','') as SupplierProductNumber
FROM Production.Product
Notice that two single quotes together as ” represents an empty string
.
LTRIM and RTRIM
LTRIM
and RTRIM
are used to remove leading and trailing spaces of string
.
LTRIM
is used to remove spaces from the beginning of the string
; whereas RTRIM
removes spaces from the end.
The general form for LTRIM
is:
LTRIM(value)
Where value is the string you wish to work with. If value is NULL, then NULL is returned.
For example consider
SELECT LTRIM(' Product Types')
Which returns ‘Product Types’.
If you want to remove spaces from both the beginning and end of a string
, you can use both functions.
SELECT RTRIM(LTRIM(' Product Types '))
Removes both the leading and trailing spaces.
These functions come in handy when importing data from text files, especially fixed formatted files.
The post Introduction to SQL Server’s Common String Functions appeared first on Essential SQL.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/