I am going to press on with my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.
I will exploring the String Operators today. These operators can be broken down into two categories: concatenation and pattern matching.
Out of the two operators, pattern matching is the most complex.
Transaction SQL has both a simple and compound version of the concatenation operator. This operator takes two strings and combines them into one.
Please note that the compound version of the operator was introduced in later versions of the database engine. In addition, assignment when declaring a variable on a single line was also introduced later.
The two examples below return the same output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- -- String operators - Concatenation -- -- Declare variables declare @front varchar(16) = 'Red '; declare @back varchar(16) = 'Riding Hood '; declare @phrase varchar(32) = ''; -- ~ simple concatenation ~ select @phrase = @front + @back; select @phrase as grimm_tale -- ~ compound concatenation ~ set @phrase = ''; set @phrase += @front; set @phrase += @back; select @phrase as grimm_tale |
|
1 2 3 4 |
-- ~ OUTPUT ~ grimm_tale -------------------------------- Red Riding Hood |
The [] operator matches any single character within the specified range or set that is specified between the brackets. The example below returns all employees with id’s like 22#.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- String Match - Character patterns USE AdventureWorks2012; GO -- Employees w/id of 22X - character match [] - 10 rows SELECT P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate FROM HumanResources.Employee AS E join Person.Person P ON E.BusinessEntityID = P.BusinessEntityID where E.BusinessEntityID like '[2][2][0-9]' GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- ~ OUTPUT ~ BusinessEntityID FirstName LastName Gender BirthDate ---------------- -------------------------------------------------- -------------------------------------------------- ------ ---------- 220 Karen Berge F 1970-01-25 221 Chris Norred M 1981-06-26 222 A. Scott Wright M 1962-10-19 223 Sairaj Uddin M 1982-01-22 224 William Vong M 1975-12-08 225 Alan Brewer M 1978-04-30 226 Brian LaMee M 1978-09-12 227 Gary Altman M 1965-03-21 228 Christian Kleinerman M 1970-02-18 229 Lori Penor F 1964-08-31 |
The _ operator matches any single character in a string comparison operation that involves pattern matching such as LIKE and PATINDEX. We have the same 10 records as output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- String Match - Character patterns USE AdventureWorks2012; GO -- Employees w/id of 22X - character match _ - 10 rows SELECT P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate FROM HumanResources.Employee AS E join Person.Person P ON E.BusinessEntityID = P.BusinessEntityID where E.BusinessEntityID like '22_' GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- ~ OUTPUT ~ BusinessEntityID FirstName LastName Gender BirthDate ---------------- -------------------------------------------------- -------------------------------------------------- ------ ---------- 220 Karen Berge F 1970-01-25 221 Chris Norred M 1981-06-26 222 A. Scott Wright M 1962-10-19 223 Sairaj Uddin M 1982-01-22 224 William Vong M 1975-12-08 225 Alan Brewer M 1978-04-30 226 Brian LaMee M 1978-09-12 227 Gary Altman M 1965-03-21 228 Christian Kleinerman M 1970-02-18 229 Lori Penor F 1964-08-31 </span> |
The % operator matches any string of zero or more characters. The output of this example adds one more record to the result set.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- String Match - Character patterns USE AdventureWorks2012; GO -- Employees w/id of 22X - character match % - 11 rows SELECT P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate FROM HumanResources.Employee AS E join Person.Person P ON E.BusinessEntityID = P.BusinessEntityID where E.BusinessEntityID like '22%' GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- ~ OUTPUT ~ BusinessEntityID FirstName LastName Gender BirthDate ---------------- -------------------------------------------------- -------------------------------------------------- ------ ---------- 22 Sariya Harnpadoungsataya M 1981-06-21 220 Karen Berge F 1970-01-25 221 Chris Norred M 1981-06-26 222 A. Scott Wright M 1962-10-19 223 Sairaj Uddin M 1982-01-22 224 William Vong M 1975-12-08 225 Alan Brewer M 1978-04-30 226 Brian LaMee M 1978-09-12 227 Gary Altman M 1965-03-21 228 Christian Kleinerman M 1970-02-18 229 Lori Penor F 1964-08-31 |
Last but not least, the ^ operator returns any strings that do not match this character. In the example below, we want to return all employees that have a first name that does not start with J. The result set has 256 matches. Shown below are the first five records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- String Match - Character patterns USE AdventureWorks2012; GO -- Employees with first name does not start with J - 256 row SELECT P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate FROM HumanResources.Employee AS E join Person.Person P ON E.BusinessEntityID = P.BusinessEntityID where p.FirstName like '[^J]%' GO |
|
1 2 3 4 5 6 7 8 9 10 |
-- ~ OUTPUT ~ BusinessEntityID FirstName LastName Gender BirthDate ---------------- -------------------------------------------------- -------------------------------------------------- ------ ---------- 1 Ken Sánchez M 1963-03-02 2 Terri Duffy F 1965-09-01 3 Roberto Tamburello M 1968-12-13 4 Rob Walters M 1969-01-23 5 Gail Erickson F 1946-10-29 </span> |
In summary, concatenation operator + was defined as part of the TSQL syntax long before the CONCAT function was ever introduced. You are supplied with 4 pattern matching functions to slice and dice your data. I am sure you will come across string operators in your scripting endeavors.
Later today, I will be researching Unary Operators.
