{"id":37133,"date":"2024-08-22T06:38:28","date_gmt":"2024-08-21T20:38:28","guid":{"rendered":"https:\/\/database.guide\/?p=37133"},"modified":"2024-08-22T06:38:30","modified_gmt":"2024-08-21T20:38:30","slug":"some-handy-t-sql-string-manipulation-techniques","status":"publish","type":"post","link":"https:\/\/database.guide\/some-handy-t-sql-string-manipulation-techniques\/","title":{"rendered":"Some Handy T-SQL String Manipulation Techniques"},"content":{"rendered":"\n

Transact-SQL (T-SQL<\/a>) offers a nice set of functions and methods for manipulating strings. Let’s look at some handy techniques that can help us work more efficiently with string data in SQL Server<\/a>.<\/p>\n\n\n\n\n\n\n\n

String Splitting with STRING_SPLIT()<\/code><\/h2>\n\n\n\n

The STRING_SPLIT()<\/code> function<\/a>, introduced in SQL Server 2016, provides an efficient way to split strings based on a delimiter:<\/p>\n\n\n\n

SELECT value\nFROM STRING_SPLIT('apple,banana,cherry', ',');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
value 
------
apple 
banana
cherry

3 row(s) returned<\/pre>\n\n\n\n
This function returns a table of strings, making it easy to use in JOIN<\/a><\/code>s or other set-based operations.<\/p>\n\n\n\n
String Aggregation with STRING_AGG()<\/code><\/h2>\n\n\n\n
The STRING_AGG()<\/code> function<\/a>, available from SQL Server 2017 onwards, allows for efficient string concatenation:<\/p>\n\n\n\n
SELECT STRING_AGG(Name, ', ') AS ProductList FROM Product;<\/code><\/pre>\n\n\n\n
Example result:<\/p>\n\n\n\n
ProductList                                                                                                                         
------------------------------------------------------------------------------------------------------------------------------------
Mountain Bike, Road Bike, Helmet, Water Bottle, Bike Pump, Cycling Shorts, Bike Light Set, Cycling Gloves, Bike Lock, Cycling Jersey

1 row(s) returned<\/pre>\n\n\n\n
This function can also be used with ORDER BY<\/code> for sorted concatenation. To do this, use the WITHIN GROUP<\/code> clause:<\/p>\n\n\n\n
SELECT \n    STRING_AGG(Name, ', ') WITHIN GROUP ( ORDER BY Name ) AS ProductList \nFROM Product;<\/code><\/pre>\n\n\n\n
Example result:<\/p>\n\n\n\n
ProductList                                                                                                                         
------------------------------------------------------------------------------------------------------------------------------------
Bike Light Set, Bike Lock, Bike Pump, Cycling Gloves, Cycling Jersey, Cycling Shorts, Helmet, Mountain Bike, Road Bike, Water Bottle

1 row(s) returned<\/pre>\n\n\n\n
Pattern Matching with LIKE<\/code><\/h2>\n\n\n\n
Complex pattern matching can be achieved using the LIKE<\/code> operator<\/a> with wildcards:<\/p>\n\n\n\n
SELECT *\nFROM Customers\nWHERE LastName LIKE '[AEIOU]%';<\/code><\/pre>\n\n\n\n
This returns customers where LastName<\/code> starts with a vowel.<\/p>\n\n\n\n
Here’s another example:<\/p>\n\n\n\n
SELECT ProductName, Description\nFROM Products\nWHERE Description LIKE '%organic%'\n   OR Description LIKE '%natural%'\n   OR Description LIKE '%eco-friendly%';<\/code><\/pre>\n\n\n\n
This query retrieves products where the description contains any of the words organic<\/code>, natural<\/code>, or eco-friendly<\/code>. The %<\/code> wildcard allows for any characters before or after the specified words.<\/p>\n\n\n\n
One more example.<\/p>\n\n\n\n
Suppose we have the following table:<\/p>\n\n\n\n
EmployeeID  Email                           
----------  --------------------------------
1           john_doe@company.co.za          
2           jane.smith@techinc.org.au       
3           mike.j@datasoft.net             
4           sarah.w@hrgroup.co.uk           
5           robert_brown@finance-corp.com.au
6           emily.davis@salesforce.io       
7           david.m@techsupport.co.nz       
8           lisa.wilson@marketpro.com.au    
9           tom.anderson@globalfin.biz   <\/pre>\n\n\n\n
Here’s a query that uses the LIKE<\/code> operator to filter the Email<\/code> column by a certain pattern:<\/p>\n\n\n\n
SELECT EmployeeID, Email\nFROM Employees\nWHERE Email LIKE '%[a-z]%[.][a-z]%@%[.]%[.]%';<\/code><\/pre>\n\n\n\n
Example result:<\/p>\n\n\n\n
EmployeeID  Email                       
----------  ----------------------------
2           jane.smith@techinc.org.au   
4           sarah.w@hrgroup.co.uk       
7           david.m@techsupport.co.nz   
8           lisa.wilson@marketpro.com.au<\/pre>\n\n\n\n
Substring Extraction with CHARINDEX()<\/code> and SUBSTRING()<\/code><\/h2>\n\n\n\n
Combine CHARINDEX()<\/a><\/code> and SUBSTRING()<\/a><\/code> for flexible text extraction:<\/p>\n\n\n\n
DECLARE @email VARCHAR(100) = 'user@example.com';\nSELECT SUBSTRING(@email, 1, CHARINDEX('@', @email) - 1);<\/code><\/pre>\n\n\n\n
Here it is when run against the Employees<\/code> table from the previous example:<\/p>\n\n\n\n
SELECT \n    SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS UserName \nFROM Employees;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
UserName    
------------
john_doe    
jane.smith  
mike.j      
sarah.w     
robert_brown
emily.davis 
david.m     
lisa.wilson 
tom.anderson<\/pre>\n\n\n\n
Advanced Parsing with PARSENAME()<\/code><\/h2>\n\n\n\n
PARSENAME()<\/a><\/code> is traditionally used for parsing object names, but it can be repurposed for general string parsing:<\/p>\n\n\n\n
SELECT PARSENAME('192.168.0.1', 4);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
192<\/pre>\n\n\n\n
This technique works well for strings with up to four period-separated parts. PARSENAME()<\/code> works backwards, and so that’s why 4<\/code> returned the first octet.<\/p>\n\n\n\n
If the separators are something other than dots, we can use REPLACE()<\/code> to change them to the relevant separator:<\/p>\n\n\n\n
SELECT PARSENAME(REPLACE('HWP-435-9002-98', '-', '.'), 2);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
9002<\/pre>\n\n\n\n
If you don’t like the fact that PARSENAME()<\/code> works backwards, there’s always the option of using the REVERSE()<\/code> function<\/a> in a couple of places to change the order around:<\/p>\n\n\n\n
SELECT REVERSE(PARSENAME(REPLACE(REVERSE('HWP-435-9002-98'), '-', '.'), 2));<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
435<\/pre>\n\n\n\n
Here, the inner REVERSE()<\/code> would’ve resulted in 435<\/code> being returned as 534<\/code> (reverse order), and so the outer REVERSE()<\/code> reverses that again to its original order.<\/p>\n\n\n\n
Bearing in mind that the PARSENAME()<\/code> wasn’t really designed for this type of string splitting (it is mainly for returning a specified part of an object name), there are usually other ways to do this type of thing (and let’s not forget our first example that uses the STRING_SPLIT()<\/code> function). Here’s an interesting discussion over at SQLTeam.com<\/a> about the pros and cons of using PARSENAME()<\/code> for string splitting along with some alternatives.<\/p>\n\n\n\n
Fuzzy Matching with SOUNDEX()<\/code><\/h2>\n\n\n\n
The SOUNDEX()<\/a><\/code> function can be used for phonetic matching:<\/p>\n\n\n\n
SELECT * \nFROM Employees \nWHERE SOUNDEX(LastName) = SOUNDEX('Smith');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
EmployeeID  FirstName  LastName  Email                      Department
----------  ---------  --------  -------------------------  ----------
2           Jane       Smith     jane.smith@techinc.org.au  Marketing 
10          Sasha      Smyth     sasha_smyth@kiwi.co.nz     Sales     
11          Rod        Smythe    rod.s@.hot.studio            Sales     <\/pre>\n\n\n\n
We can see that although we only entered Smith<\/code>, the SOUNDEX()<\/code> function ensured that it also matched other names that sound similar to Smith<\/code>.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
These handy T-SQL string manipulation techniques can significantly enhance our ability to work with text data in SQL Server. <\/p>\n\n\n\n
Remember to consider performance implications when working with large datasets, and always test thoroughly in a non-production environment before implementing these techniques in production systems.<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Transact-SQL (T-SQL) offers a nice set of functions and methods for manipulating strings. Let’s look at some handy techniques that can help us work more efficiently with string data in SQL Server.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[10,77,84,61],"class_list":["post-37133","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-how-to","tag-mssql","tag-string","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=37133"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37133\/revisions"}],"predecessor-version":[{"id":38593,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/37133\/revisions\/38593"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=37133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=37133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=37133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}