Working with text: string functions

Text arrives messy: names in the wrong case, phone numbers with spaces, emails you need the domain of. T-SQL has a toolbox of string functions for cleaning and reshaping it inside a query. Two rules hold for all of them: positions start at 1, and a NULL input gives a NULL result (except where noted).

Reshaping names

1SELECT FirstName, LastName,2       UPPER(LastName) + N', ' + LEFT(FirstName, 1) + N'.' AS Label,3       LEN(FirstName + LastName) AS Letters4FROM dbo.Customers5WHERE CustomerId <= 4;

FirstName  LastName  Label       Letters
---------  --------  ----------  -------
Asha       Patel     PATEL, A.         9
Ben        Carter    CARTER, B.        9
Chloe      Evans     EVANS, C.        10
Dev        Singh     SINGH, D.         8

Cutting at a character

CHARINDEX finds where something is; LEFT and SUBSTRING cut there. The first word of each product name ends at the first space. Adding a space to the end (Name + N' ') makes sure there always is one:

1SELECT Name,2       CHARINDEX(N' ', Name) AS FirstSpace,3       LEFT(Name, CHARINDEX(N' ', Name + N' ') - 1) AS FirstWord4FROM dbo.Products5WHERE CategoryId IN (2, 3);

Name                 FirstSpace  FirstWord
-------------------  ----------  ---------
Cement 25 kg                  7  Cement
Sand 25 kg                    5  Sand
Timber 2x4 2.4 m              7  Timber
Plywood sheet 18 mm           8  Plywood

The same idea splits an email address into the part before and after the @. Ben has no email, so every expression on it is NULL, without any error:

1SELECT FirstName, Email,2       LEFT(Email, CHARINDEX(N'@', Email) - 1) AS UserName,3       SUBSTRING(Email, CHARINDEX(N'@', Email) + 1, 100) AS Domain4FROM dbo.Customers5WHERE CustomerId IN (1, 2, 6);

FirstName  Email              UserName  Domain
---------  -----------------  --------  -----------
Asha       asha@example.com   asha      example.com
Ben        NULL               NULL      NULL
Farid      farid@example.com  farid     example.com

Cleaning and joining

REPLACE removes or swaps characters, and CONCAT_WS builds text from parts while skipping the NULL ones, so no stray commas appear:

1SELECT FirstName,2       REPLACE(Phone, ' ', '') AS DialString,3       CONCAT_WS(N' | ', City, Email, Phone) AS Contact4FROM dbo.Customers5WHERE CustomerId IN (2, 3, 5);

FirstName  DialString   Contact
---------  -----------  -------------------------
Ben        01904555010  York | 01904 555 010
Chloe      NULL         Leeds | chloe@example.com
Ella       NULL         ella@example.com

Splitting a list

STRING_SPLIT turns one delimited string into rows, one per item. It is a table-valued function, so it goes in FROM, not in the SELECT list, and its column is called value:

1SELECT TRIM(value) AS Item2FROM STRING_SPLIT(N'cement, sand ,timber,  paint', N',');

Item
------
cement
sand
timber
paint

Collation: does case matter?

Whether 'A' = 'a', and whether REPLACE and CHARINDEX care about case, is decided by the collation of the column or database. The default for English-language installs, SQL_Latin1_General_CP1_CI_AS, is Case-Insensitive and Accent-Sensitive. You can override it for one comparison:

1SELECT IIF(N'Leeds' = N'LEEDS', 'equal', 'different') AS DefaultCollation,2       IIF(N'Leeds' = N'LEEDS' COLLATE Latin1_General_CS_AS, 'equal', 'different') AS CaseSensitive;

DefaultCollation  CaseSensitive
----------------  -------------
equal             different

1SELECT LastName + N', ' + LEFT(FirstName, 1) + N'.' AS Name,2       LEFT(Email, CHARINDEX(N'@', Email) - 1) AS UserName3FROM dbo.Customers;

Name        UserName
----------  --------
Patel, A.   asha
Carter, B.  NULL
Evans, C.   chloe
Singh, D.   dev
Brown, E.   ella
Khan, F.    farid
Lee, G.     NULL
Müller, Z.  zoe