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. <\/p>\n
I will exploring the String Operators<\/a> today. These operators can be broken down into two categories: concatenation and pattern matching. <\/p>\n Out of the two operators, pattern matching is the most complex.<\/p>\n Transaction SQL has both a simple<\/a> and compound<\/a> version of the concatenation operator. This operator takes two strings and combines them into one. <\/p>\n 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.<\/p>\n The two examples below return the same output. <\/p>\n The [] operator<\/a> 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#.<\/p>\n The _ operator<\/a> matches any single character in a string comparison operation that involves pattern matching such as LIKE<\/a> and PATINDEX<\/a>. We have the same 10 records as output.<\/p>\n The % operator<\/a> matches any string of zero or more characters. The output of this example adds one more record to the result set. <\/p>\n Last but not least, the ^ operator<\/a> 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.<\/p>\n In summary, concatenation operator + was defined as part of the TSQL syntax long before the CONCAT<\/a> 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. <\/p>\n\r\n--\r\n-- String operators - Concatenation\r\n--\r\n\r\n-- Declare variables\r\ndeclare @front varchar(16) = 'Red ';\r\ndeclare @back varchar(16) = 'Riding Hood ';\r\ndeclare @phrase varchar(32) = '';\r\n\r\n-- ~ simple concatenation ~\r\nselect @phrase = @front + @back;\r\nselect @phrase as grimm_tale\r\n\r\n-- ~ compound concatenation ~\r\nset @phrase = '';\r\nset @phrase += @front;\r\nset @phrase += @back;\r\nselect @phrase as grimm_tale\r\n<\/pre>\n<\/p>\n
\r\n-- ~ OUTPUT ~\r\ngrimm_tale\r\n--------------------------------\r\nRed Riding Hood \r\n\r\n<\/pre>\n<\/p>\n
\r\n-- String Match - Character patterns\r\nUSE AdventureWorks2012;\r\nGO \r\n\r\n-- Employees w\/id of 22X - character match [] - 10 rows\r\nSELECT \r\n P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate\r\nFROM \r\n HumanResources.Employee AS E join Person.Person P\r\nON \r\n E.BusinessEntityID = P.BusinessEntityID\r\nwhere \r\n E.BusinessEntityID like '[2][2][0-9]'\r\nGO\r\n<\/pre>\n<\/p>\n
\r\n-- ~ OUTPUT ~\r\nBusinessEntityID FirstName LastName Gender BirthDate\r\n---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------\r\n220 Karen Berge F 1970-01-25\r\n221 Chris Norred M 1981-06-26\r\n222 A. Scott Wright M 1962-10-19\r\n223 Sairaj Uddin M 1982-01-22\r\n224 William Vong M 1975-12-08\r\n225 Alan Brewer M 1978-04-30\r\n226 Brian LaMee M 1978-09-12\r\n227 Gary Altman M 1965-03-21\r\n228 Christian Kleinerman M 1970-02-18\r\n229 Lori Penor F 1964-08-31\r\n\r\n<\/pre>\n<\/p>\n
\r\n-- String Match - Character patterns\r\nUSE AdventureWorks2012;\r\nGO \r\n\r\n-- Employees w\/id of 22X - character match _ - 10 rows\r\nSELECT \r\n P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate\r\nFROM \r\n HumanResources.Employee AS E join Person.Person P\r\nON \r\n E.BusinessEntityID = P.BusinessEntityID\r\nwhere \r\n E.BusinessEntityID like '22_'\r\nGO\r\n<\/pre>\n<\/p>\n
\r\n-- ~ OUTPUT ~\r\nBusinessEntityID FirstName LastName Gender BirthDate\r\n---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------\r\n220 Karen Berge F 1970-01-25\r\n221 Chris Norred M 1981-06-26\r\n222 A. Scott Wright M 1962-10-19\r\n223 Sairaj Uddin M 1982-01-22\r\n224 William Vong M 1975-12-08\r\n225 Alan Brewer M 1978-04-30\r\n226 Brian LaMee M 1978-09-12\r\n227 Gary Altman M 1965-03-21\r\n228 Christian Kleinerman M 1970-02-18\r\n229 Lori Penor F 1964-08-31\r\n\r\n<\/span><\/pre>\n<\/p>\n
\r\n-- String Match - Character patterns\r\nUSE AdventureWorks2012;\r\nGO \r\n\r\n-- Employees w\/id of 22X - character match % - 11 rows\r\nSELECT \r\n P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate\r\nFROM \r\n HumanResources.Employee AS E join Person.Person P\r\nON \r\n E.BusinessEntityID = P.BusinessEntityID\r\nwhere \r\n E.BusinessEntityID like '22%'\r\nGO\r\n<\/pre>\n<\/p>\n
\r\n-- ~ OUTPUT ~\r\nBusinessEntityID FirstName LastName Gender BirthDate\r\n---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------\r\n22 Sariya Harnpadoungsataya M 1981-06-21\r\n220 Karen Berge F 1970-01-25\r\n221 Chris Norred M 1981-06-26\r\n222 A. Scott Wright M 1962-10-19\r\n223 Sairaj Uddin M 1982-01-22\r\n224 William Vong M 1975-12-08\r\n225 Alan Brewer M 1978-04-30\r\n226 Brian LaMee M 1978-09-12\r\n227 Gary Altman M 1965-03-21\r\n228 Christian Kleinerman M 1970-02-18\r\n229 Lori Penor F 1964-08-31\r\n\r\n<\/pre>\n<\/p>\n
\r\n-- String Match - Character patterns\r\nUSE AdventureWorks2012;\r\nGO \r\n\r\n-- Employees with first name does not start with J - 256 row\r\nSELECT \r\n P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate\r\nFROM \r\n HumanResources.Employee AS E join Person.Person P\r\nON \r\n E.BusinessEntityID = P.BusinessEntityID\r\nwhere \r\n p.FirstName like '[^J]%'\r\nGO\r\n<\/pre>\n<\/p>\n
\r\n-- ~ OUTPUT ~\r\nBusinessEntityID FirstName LastName Gender BirthDate\r\n---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------\r\n1 Ken S\u00e1nchez M 1963-03-02\r\n2 Terri Duffy F 1965-09-01\r\n3 Roberto Tamburello M 1968-12-13\r\n4 Rob Walters M 1969-01-23\r\n5 Gail Erickson F 1946-10-29\r\n\r\n<\/span><\/pre>\n<\/p>\n