Chapter 11 – Working with Strings
“It’s always been and always will be the same in the world: the horse does the work and the coachman is tipped.”
- Anonymous
The ASCII Function
The example below shows you how to convert
characters into the integer ASCII value.
Syntax: ASCII (string)
SELECT
ASCII('H') as AsciiH
,ASCII('o') as AsciiO
,ASCII('w') as AsciiW
,ASCII('d') as AsciiD
,ASCII('y') as AsciiY
The example above shows you how to convert characters into the integer ASCII value.
The CHAR Function
The example below shows you how to convert
the integer ASCII value into characters.
Syntax: CHAR (integer)
SELECT
CHAR(72) As CharH
,CHAR(111) As CharO
,CHAR(119) As CharW
,CHAR(100) As CharD
,CHAR(121) As CharY ;
The example above shows you how to convert the integer ASCII value into characters.
The UNICODE Function
The UNICODE function returns the Unicode integer value for the first character of the character or input expression.
Syntax: UNICODE (string)
SELECT
UNICODE('H') AS UniH
,UNICODE('o') AS UniO
,UNICODE('w') AS UniW
,UNICODE('d') AS UniD
,UNICODE('y') AS UniY ;
The example above shows you how to convert characters into the UNICODE value.
The NCHAR Function
The NCHAR function takes the integer values and converts them back into characters.
Syntax: NCHAR (Integer)
SELECT
NCHAR(72) AS NcaH
,NCHAR(111) AS NcaO
,NCHAR(119) AS NcaW
,NCHAR(100) AS NcaD
,NCHAR(121) AS NcaY ;
The example above shows you how to convert integers back to characters.
The LEN Function
The LEN function returns the number of characters in an input string. (Ending spaces are automatically excluded for CHAR data types)
Syntax: LEN (string)
SELECT First_Name
,LEN(First_Name) AS Lnth
,Last_Name
,LEN(Last_Name) AS Lnth
FROM Employee_Table
The LEN function returns the number of characters in the input string and not necessarily the number of bytes.
The DATALENGTH Function
The DATALENGTH function returns the number of characters in an input string. (Ending spaces are automatically included for CHAR data types)
Syntax: DATALENGTH (string)
SELECT
First_Name
,DATALENGTH(First_Name) AS Lnth
,Last_Name
,DATALENGTH(Last_Name) AS Lnth
FROM Employee_Table
The DATALENGTH function returns the number of characters in the input string and not necessarily the number of bytes. The difference between the LEN and the DATALENGTH functions is that the LEN function excludes trailing spaces. The DATALENGTH function counts them. Notice that each length is 20 characters for the Last_Name lengths.
Concatenation
See those + signs? Those represent concatenation. That allows you to combine multiple columns into one column. The + in this example has combined the first name, then a single space, and then the last name to get a new column called ‘Full name’. We brought back the full name of Squiggy Jones.
The RTRIM and LTRIM Command trims Spaces
RTRIM Query
SELECT
Last_Name
,RTRIM(Last_Name) AS Trim_Trailing_Spaces
FROM Employee_Table ;
LTRIM Query
SELECT
Last_Name
,LTRIM(Last_Name) AS Trim_Leading_Spaces
FROM Employee_Table ;
Trimming Both Leading and Trailing Spaces Query
SELECT
Last_Name
,LTRIM(RTRIM(Last_Name)) AS Trim_Spaces_Leading_Trailing
FROM Employee_Table ;
The RTRIM command trims trailing spaces from a character string. The LTRIM trims leading spaces from a character string. The LTRIM(RTRIM) combination trims both leading and trailing spaces from a character string . .
The SUBSTRING Command
First_Name |
Quiz |
Squiggy |
qui |
John |
ohn |
Richard |
ich |
Herbert |
erb |
Mandee |
and |
Cletus |
let |
William |
ill |
Billy |
ill |
Loraine |
ora |
This is a SUBSTRING. The substring is passed two parameters, and they are the starting position of the string and the number of positions to return (from the starting position). The above example will start in position 2 and go for 3 positions!
Using SUBSTRING to move Backwards
First_Name |
Before1 |
Squiggy |
Squig |
John |
John |
Richard |
Richa |
Herbert |
Herbe |
Mandee |
Mande |
Cletus |
Cletu |
William |
Willi |
Billy |
Billy |
Loraine |
Lorai |
A starting position of zero moves one space in front of the beginning. Notice that our FOR Length is 6 so ‘Squiggy’ turns into ‘ Squig’. The point being made here is that both the starting position and ending positions can move backwards which will come in handy as you see other examples.
How SUBSTRING Works with a Starting Position of -1
First_Name |
Before2 |
Squiggy |
S |
John |
J |
Richard |
R |
Herbert |
H |
Mandee |
M |
Cletus |
C |
William |
W |
Billy |
B |
Loraine |
L |
A starting position of -1 moves two spaces in front of the beginning. Notice that our FOR Length is 3, so each name delivers only the first initial. The point being made here is that both the starting position and ending positions can move backwards which will come in handy as you see other examples.
How SUBSTRING Works with an Ending Position of 0
First_Name |
WhatsUp |
Squiggy |
|
John |
|
Richard |
|
Herbert |
|
Mandee |
|
Cletus |
|
William |
|
Billy |
|
Loraine |
|
In our example above, we start in position 3, but we go for zero positions, so nothing is delivered in the column. That is what’s up!
Concatenation and SUBSTRING
Of the three items being concatenated together, what is the first item of concatenation in the example above? The first initial of the First_Name. Then, we concatenated a literal space and a period. Then, we concatenated the Last_Name.
SUBSTRING and Different Aliasing
SELECT
Phone_Number
,First3digits = SUBSTRING(Phone_Number, 1, 3)
,Exchange = SUBSTRING(Phone_Number, 5,4)
FROM Customer_Table
WHERE Phone_Number LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
Above, we are using the Substring commands to extract certain portions of the Phone_Number. Notice that the column names are materialized at the beginning of the line. This is almost like a reverse alias.
The LEFT and RIGHT Functions
The LEFT and RIGHT functions are abbreviations of the SUBSTRING function. They return a requested number of characters from the left or right end of the input string.
Syntax: LEFT(string, n), RIGHT(string, n)
SELECT
First_Name
,LEFT (First_Name , 1) AS First_Initial
,Last_Name
,Right (RTRIM(Last_name), 2) AS "Last Two Letters"
FROM Employee_Table
WHERE Dept_No in (400) ;
In our example above, our result set will have the First_Name and Last_Name coming back, but we also use the LEFT and RIGHT functions to produce the first letter of the First_Name and the last two letters of the Last_Name. We filtered the rows with an additional WHERE clause to only bring back three rows. Notice the RTRIM of Last_Name. This is necessary because the Last_Name column has a data type of Character 20. This is padded with spaces.
Four Concatenations Together
Why did we TRIM the Last_Name? To get rid of the spaces or the output would have looked odd. How many items are being concatenated in the example above? There are 4 items concatenated. We start with the Last_Name (after we trim it), then we have a single space, then we have the First Initial of the First Name, and then we have a Period.
The DATALENGTH Function and RTRIM
The DATALENGTH function returns the number of characters in an input string.
(Ending spaces are automatically included for CHAR data types)
Syntax: DATALENGTH (string)
SELECT
First_Name
,DATALENGTH(First_Name) AS Lnth
,Last_Name
,DATALENGTH(RTRIM(Last_Name)) AS Lnth
FROM Employee_Table
The DATALENGTH function returns the number of characters in the input string and not necessarily the number of bytes. The difference between the LEN and the DATALENGTH functions is that the LEN function excludes trailing spaces, however the DATALENGTH function counts them. Use either the LEN function or merely RTRIM with DATALENGTH.
A Visual of the TRIM Command Using Concatenation
When you use the RTRIM command on a column, that column will have trailing spaces removed.
CHARINDEX Function Finds a Letter(s) Position in a String
Tell this function what character(s) to look for in a string, and optionally, what starting position to first start looking. If it does not find the character(s) in the string it returns a 0. It also only reports the first occurrence.
Syntax: CHARINDEX(substring, string[, start_pos])
SELECT Last_Name
,CHARINDEX ('e', Last_Name) AS Find_E
,CHARINDEX ('f', Last_Name) AS Find_F
,CHARINDEX ('th', Last_Name) AS Find_TH
,CHARINDEX ('in', Last_Name, 6) AS Find_es_after_6
FROM Employee_Table
WHERE Last_Name IN ('Smith', 'Smythe', 'Strickling', 'Coffing')
ORDER BY 1 DESC;
Strickling does not have an 'e', 'f' or 'th' in it, but it does have an 'in' starting in position 8. Coffing shows only the first 'f' in position 3, but notice that Coffing also has an 'in', however we stated to start looking in position 6, thus a zero was returned to indicate it didn't find an occurrence. Smith and Smythe both have a 'th' starting in position 4.
The CHARINDEX Command is brilliant with SUBSTRING
Last_Name |
Last_Two_Letters |
Smythe |
he |
Strickling |
ng |
Chambers |
rs |
Harrison |
on |
Coffing |
ng |
Smith |
th |
Jones |
es |
Larkins |
ns |
Reilly |
ly |
What was the starting position of the Substring in the above query? It uses a subquery.
The CHARINDEX Command Using a Literal
(No column name) |
21 |
We are looking for the phrase May flowers. This starts in position 21 of the substring
PATINDEX Function
The PATINDEX, better named "Pattern Index" will find patterns in an argument somewhat similar to the LIKE command. The following example will show how to find the first occurrence of a digit within a string.
Syntax: PATINDEX(pattern, string)
SELECT
PATINDEX('%[0-9]%', 'July 4th Holiday') as Number_Position;
Give me the position of
any number between 0-9
in the string
Number_Position |
6 |
The "Pattern Index", referred to as PATINDEX will look for a pattern in a string and give you the position of the first character in the pattern. Above, we are using the literal 'July 4th Holiday', but we could have used a column value. The number 4 is in the 6th position of the value.
PATINDEX Function to Find a Character Pattern
The PATINDEX, better named "Pattern Index" will find patterns in an argument somewhat similar to the LIKE command.
The example below will find any occurrence where the column Street has a 3 before the St.
Syntax: PATINDEX(pattern, string)
SELECT
Subscriber_No,
Street,
PATINDEX('%[3]%St%', Street) As "Street_3"
FROM Addresses
The "Pattern Index", referred to as PATINDEX will look for a pattern in a string and give you the position of the first character in the pattern. Above, we are using the column Street to see if there is a 3 before the St. Notice that we have two hits and they are both in the 3rd position of the column Street.
SOUNDEX Function to Find a Sound
The SOUNDEX, better named "Sound" will display similar sounding items.
The example below will find any Last_Name that sounds like 'Smith'.
Syntax: SOUNDEX(String)
SELECT DISTINCT
SOUNDEX(Last_Name) SoundsLike1
,SOUNDEX('Smith') SoundsLike2
,Last_Name
FROM Employee_Table
WHERE SOUNDEX(Last_Name) = SOUNDEX('Smith')
Call center employees often look up customers by last name while speaking with the customer on the phone. The employees would like to guess at the spelling of the name to narrow the search results and then work with the customer to determine the appropriate spelling. This is what the SOUNDEX function does. Above, we are looking at anyone who has a name that sounds like 'Smith'. We got two results back in 'Smith' and 'Smythe'.
DIFFERENCE Function to Quantile a Sound
The DIFFERENCE function will display similar sounding items and give
them a quantile of 4 (high similarity) to a low of 0 (low similarity).
SELECT DISTINCT
SOUNDEX(Last_Name) AS Sound1
,SOUNDEX('smith') AS Sound_Smith
,DIFFERENCE(Last_Name, 'Smith') as High4Low0
,Last_Name
FROM Employee_Table
ORDER BY 3 DESC ;
Call center employees often look up customers by last name while speaking with the customer on the phone. The employees would like to guess at the spelling of the name to narrow the search results and then work with the customer to determine the appropriate spelling. The SOUNDEX and DIFFERENCE functions can both be used. Above, we are using the DIFFERENCE function to show how close the name 'Smith' is to other Last_Name values.
The REPLACE Function
The REPLACE function replaces all occurrences
of substring1 in the string with substring2.
Syntax: REPLACE(string, substring1, substring2)
SELECT Customer_Name
,REPLACE (Customer_Name, ' ', '_') AS Under_Score
,Phone_Number
,REPLACE (Phone_Number, '-', ' ') AS No_Dash
FROM Customer_table
The RELACE function replaces a value for another in a string. Above, we have replaced the spaces in a Customer Name with underscores. In the Phone Number we have replace the dashes (-) with a space.
LEN and REPLACE Functions for Number of Occurrences
Last_Name |
Num_of_Occur |
Strickling |
1 |
Chambers |
1 |
Harrison |
2 |
Larkins |
1 |
Reilly |
1 |
The LEN function returns the number of characters in an input string.
Syntax: LEN (string)
The REPLACE function replaces all occurrences
of substring1 in the string with substring2.
Syntax: REPLACE(string, substring1, substring2)
The RELACE function and LEN function can be combined to find the number of occurrences of a character. You can use the REPLACE function to count the number of occurrences of a character within a string. To do this, you replace all occurrences of the character with an empty string (zero characters) and calculate the original length of the string minus the new length.
REPLICATE Function
The REPLICATE function replicates a string a requested number of times.
Syntax: REPLICATE(string, n)
SELECT Last_Name
,Class_Code
,REPLICATE(Class_Code, 3) AS Repeat_3_Times
,REPLICATE('Go Wildcats! ', 2) AS UofA
FROM Student_Table
The REPLICATE function replicates a string a number of times. Above, notice we replicated the class_code column 3 times. Also notice that we replicated a literal value of 'Go Wildcats! ' 2 times. Did you notice that Johnson had a null value for his Class_Code? The Null value did not replicate.
STUFF Function
The STUFF function works on a character string and will put STUFF
where you want STUFF after deleting STUFF.
Syntax: STUFF(string, pos, delete_length, insertstring)
The STUFF function operates on an input parameter string. It deletes as many characters as the number specified in the delete_length parameter, starting at the character position specified in the pos input parameter. The function inserts the string specified in the insertstring parameter in position pos. If you decide to insert a string and not delete anything, you can specify a length of 0 as the third argument.
STUFF without Deleting Function
The STUFF function works on a character string and will put STUFF
where you want STUFF after deleting STUFF.
Syntax: STUFF(string, pos, delete_length, insertstring)
Course_Name |
Course_Added |
Advanced SQL |
Course: Advanced SQL |
Database Administration |
Course: Database Administration |
Introduction to SQL |
Course: Introduction to SQL |
Physical Database Design |
Course: Physical Database Design |
SQL Server Concepts |
Course: SQL Server Concepts |
V2R3 SQL Features |
Course: V2R3 SQL Features |
Above, we decided not to delete anything, but to insert a string called 'Course: ', so we specified a length of 0 as the third argument. The STUFF function operates on an input parameter string. It deletes as many characters as the number specified in the delete_length parameter, starting at the character position specified in the pos input parameter. The function inserts the string specified in the insertstring parameter in position pos.
UPPER and lower Functions
The UPPER and LOWER functions convert the input string to either all
uppercase or lowercase characters.
Syntax: UPPER(string), LOWER(string)
SELECT First_Name
,UPPER (First_Name) as "Upper Case"
,lower(First_Name) as "Lower Case"
FROM Student_Table
The UPPER and LOWER functions convert the input string to either all uppercase or lowercase characters.