SQL Server String Operations Summary

SQL Server supports two character data types---general and Unicode, and general types include char and varchar;unicode including nchar and nvarchar. Each character in the general occupies one byte of storage, while the Unicode data type consumes 2 bytes per character and requires 4 bytes for a surrogate pair. If you select a regular character type for a column, you will be restricted to use only one language except English. You can use Unicode data types to support multiple languages. If you need to store character data in more than one language, you should use Unicode character types.

---1, string connection using the "+" operator

Select Empid,firstname + "+ LastName as FullName from HR. Employees

--the result of connecting NULL directly outputs NULL

Select custid,country,region,city, Country + ' +region + ' +city as location from sales. Customers

---2, SUBSTRING function substring (string,start,length) Here it is important to note that the starting position of SQL starts from 1; The SUBSTRING function can extract a new string from a string

Select SUBSTRING (' ABCD ', up)--Return AB

Select SUBSTRING (' ABCD ', 1,6)--if the length exceeds the length of the string without an error, it is output to the last digit of the string

--3, left and right functions; These functions are simplified forms of substring, which return a new string of the specified length from the Ieft of the specified string (string,n) to (String,n)

Select Left (' ABCD ', 1)--Return a

Select right (' ABCD ', 2)--Back to CD

--4, Len, and datalength functions; The Len function returns the length of the specified string; datalength returns the byte length of the specified string;

--each character in a regular data type needs one byte to store, so the two function return values are the same;
--Unicode characters that require two bytes per character (in most cases)

Select LEN (' ABCD ')--return 4

Select Datalength (' ABCD ')--return 4

Select Datalength (N ' ABCD ')--return 8

--5, charindex function; Returns the position of the first occurrence of a substring in a string

Select CHARINDEX (' abc ', ' Xyzabcdefabc ')--return 4

--You can select a third parameter to specify where to start the lookup

Select CHARINDEX (' abc ', ' Xyzabcdefabc ', 5)--Return 10

--6, patindex function; This function is similar to the LIKE predicate in SQL

Select PATINDEX ('%1% ', ' ABCD1234ABCD ')--return 5

--7, replace (STRING,STR1,STR2) function; Replace all str1 in string with str2

Select REPLACE (' Abcd1234abcd ', ' A ', ' # ')

--Another use of the function; count the number of occurrences of a character in a string

Select Len (' ABCDE ')-Len (replace (' ABCDE ', ' a ', ')--a occurs 1 times in a string

Select Len (' Abcdeabcd ')-Len (replace (' ABCDEABCD ', ' a ', "))--a appears in the String 2 times

---8, replicate function; Repeats a string by a specified number of times

Select REPLICATE (' abc# ', 3)

--Actual application vendor number fixed bit 10 bit, less than 10 bit in front 0

Select Supplierid,right (REPLICATE (' 0 ', 9) + cast (SupplierID as varchar), as Strsupplierid from Production.Products

---9, stuff function; Allows a specified number of strings to be removed from a string and inserts a new substring of substitution

--Stuff (string,index,delete_length,indertstring)
Select STUFF (' abc ', 1, 2, ' 12345 ')--return 12345c

Select STUFF (' ABCD ', 4, 1, ' 123 ')--return to abc123

--10, upper and lower functions; return uppercase and lowercase strings

Select UPPER (' ABCD ')--return ' ABCD '

Select LOWER (' ABCD ')--return ' ABCD '

---11, LTrim, RTrim function to remove left or right empty string
Select LTRIM (' abc ')--return ' ABC '

Select Rtrim (' abc ')--return ' ABC '

--12, the Format function; Returns the input value in the specified format-note that this function can only be used if it is above SQL Server2012
--Example 8 returns a string of a specified length that can be done simply with format
Select FORMAT (' 123 ', ' 0000000000 ')

Select FORMAT (getdate (), ' yyyy-mm-dd ')

SQL Server String Operations Summary