Using String Functions With SQL

In this post, we’ll quickly look at some string functions that we can use in some SQL languages (examples are all T-SQL and some of these are supported in other SQL languages). We’ll start with five common string functions — LEN, CHARINDEX, SUBSTRING, LEFT and RIGHT. In a query window, let’s execute the below code and we’ll see the results that are in the comments:

DECLARE @string VARCHAR(12) = 'abcde.fghij'

SELECT LEN(@string) ----11
SELECT CHARINDEX('.',@string) ----6
SELECT SUBSTRING(@string,0,3) ----ab
SELECT SUBSTRING(@string,1,3) ----abc
SELECT LEFT(@string,3) ----abc
SELECT RIGHT(@string,3) ----hij

• LEN: returns the length of the string. In this case, the actual length of the string is 11.
• CHARINDEX: returns the position of the character that we pass in. In this case, the . is 6th character.
• SUBSTRING: returns a part of the string starting from a position up to a position.
• LEFT: returns a part of a string starting from the left most character up to a position.
• RIGHT: returns a part of a string starting from the right most character up to a position; while you are starting at the right most character, you still count left.

Question

Suppose that we have a column with names that leads with prefixes such as Mr./Mrs./Dr. before the first and last name. We want to return only the name without the leading prefix. What is a solution that we can use? I’ll answer this question at the end of the post. For now, we’ll look at some applied examples with other data.

Applied Example: Parsing Names

Let’s apply these functions by applying these functions to an example where we try to parse a first name and last name out of a column that has both and is separated by a comma. I’ve written this code so that you can run it in a new query window without highlighting parts of the code — it will drop the table if it exists, otherwise it will create the table and output various results.

---- Allows easy execution
IF OBJECT_ID('StringTest') IS NOT NULL
BEGIN
 DROP TABLE StringTest
END 

CREATE TABLE StringTest(
 String VARCHAR(100)
)

INSERT INTO StringTest 
VALUES ('Jefferson,John')
 , ('Jefferson,Jane')
 , ('Johnson,Sarah')
 , ('Johnson,James')

SELECT
 ---- Base string along with base meta details of strings:
 String AS String_Base
 , LEN(String) AS String_Length
 , CHARINDEX(',',String) AS CharIndexString
 ---- Getting the last name:
 , LEFT(String,CHARINDEX(',',String)) AS String_Left
 , LEFT(String,CHARINDEX(',',String)-1) AS String_LeftMinus1 --- this returns the last name without spaces
 , LEFT(String,CHARINDEX(',',String)+1) AS String_LeftPlus1
 ---- Getting the first name:
 , RIGHT(String,CHARINDEX(',',String)) AS String_Right ---- Counterintuitive, but starting from the RIGHT then counting characters to the left
 , RIGHT(String,LEN(String)-CHARINDEX(',',String)) String_UsingRight
 , SUBSTRING(String,CHARINDEX(',',String)+1,LEN(String)-CHARINDEX(',',String)) String_UsingSubstring
FROM StringTest

The result is:

We’ll note that we can use a combination of both types of functions to get the first and last name. We’ll also see how some of this functionality returns the incorrect information because of where the position starts or ends — we don’t want results with commas in this case.

Answer

Using the same data structure, we’ll return to solving our earlier question:

TRUNCATE TABLE StringTest ---- Empties the table

INSERT INTO StringTest 
VALUES ('Mr. John Doe')
 , ('Mrs. Jane Doe')
 , ('Mr. James Johnson')
 , ('Dr. Sarah Johnson')

SELECT RIGHT(String,LEN(String)-CHARINDEX('.',String)) Answer_UsingRight
 , SUBSTRING(String,CHARINDEX('.',String)+1,LEN(String)-CHARINDEX('.',String)) Answer_UsingSubstring
FROM StringTest

Both of these will return the name without the prefix.

Note: all images in the post are either created through actual code runs or from Pixabay. The written content of this post is copyright; all rights reserved. None of the written content in this post may be used in any artificial intelligence.