This workbench finishes of what has been a three-part series of string functions. In it, we introduce the idea of using XML to provide a very simple array for doing string handling. This allows us to use functions for searching and splitting strings that will be familiar to users of procedural languages such as PHP and Python.<\/p>\n
The first part, TSQL String Array Workbench<\/a> showed how the basics worked and then demonstrated how it could be used with a PHP-style string function. What inspired us to write this workshop was when Phil had to endure a PHP programmer sounding off about how much better PHPs string handling as than TSQL. He then made the discovery that it was actually possible to pass a string in an XML parameter, detect the fact and convert it to a single-item list, so as to emulate the facility of PHP and Python to pass either lists or single strings.<\/p>\n Of course, this principle could be extended to arrays and matrices. We don’t handle the representation of lists, arrays and matrices in any standard way, as this would be a distraction at this stage, and multi-dimensional lists of arrays aren’t used much for strings.<\/p>\n We then got rather diverted by the Python string functions (now string methods) and so wrote the SQL String User Function Workbench: part 1 <\/a> that emulated all the python string functions that didn’t have lists as parameters so we didn’t use the XML array mechanism. Unfortunately there were a whole group that did, so here to round things up are the…<\/p>\n SQL String User Functions (from Python) that can use lists<\/strong><\/p>\n Return an array of the words in the string, using Now we simply output the temporary table variable as XML using our standard string-array format.<\/p>\n So now we test it out (The real test rig is longer and more boring).<\/p>\n Return a list of the lines in the string, breaking at line boundaries. Line breaks are not included in the resulting list unless keepends is given and true. p.s. This is such a simple modification to ‘Split’ that you wonder why they bothered.<\/p>\n Note: in SQL Server 2016, there is a built-in function to do this called STRING_SPLIT<\/p>\n Return non-zero if the string contains the specified substring, otherwise return Return non-zero if the string ends with the suffix, otherwise return Return non-zero if the string starts with the prefix, otherwise return Return non-zero if the string contains the substring, otherwise returns 0. substring can also be a list of substrings to look for. With optional start, test string beginning at that position. With optional end, stop comparing string at that position.<\/p>\n Joins together the given array as a string with the Split the string at the first occurrence of Split the string at the first occurrence of Contents<\/h2>\n
\n
Split Function <\/b><\/h3>\n
@delimiter<\/code> as a delimiter. If @maxsplit<\/code> is given, at most @maxsplit<\/code> splits are done. (thus, the list will have at most maxsplit+1<\/code> elements). If @maxsplit<\/code> is not specified, then there is no limit on the number of splits (all possible splits are made). Consecutive delimiters are not grouped together and are deemed to delimit empty strings. The sep<\/code> argument may consist of several characters. If @Delimiter<\/code> is not specified or is None<\/code>, a different splitting algorithm is applied. First, whitespace characters spaces, tabs, newlines, returns, and formfeeds) are stripped from both ends. Then, words are separated by arbitrary length strings of whitespace characters. Consecutive whitespace delimiters are treated as a single delimiter. Splitting an empty string or a string consisting of just whitespace returns an empty list. P.S. We took this second ‘splitting algorithm’ to mean that a list of the words was required. Our solution is only tested for English and will need fine tuning for other languages. Phil swore ages ago that Hell would freeze over before he ever published yet another string-splitting algorithm. We may have hit on a solution to global warming here.<\/p>\nIF OBJECT_ID(N'split') IS NOT NULL\r\n DROP FUNCTION split\r\nGO\r\n<\/a>\r\nCREATE FUNCTION [dbo].[split]\r\n (\r\n @String VARCHAR(8000),\r\n @Delimiter VARCHAR(255) = NULL,\r\n @MaxSplit INT = NULL\r\n \r\n )\r\nRETURNS XML\r\nAS BEGIN\r\n DECLARE @results TABLE\r\n (\r\n seqno INT IDENTITY(1, 1),\r\n Item VARCHAR(MAX)\r\n )\r\n DECLARE @xml XML,\r\n @HowManyDone INT, --index of current search\r\n @HowMuchToDo INT,--How much more of the string to do\r\n @StartOfSplit INT,\r\n @EndOfSplit INT,\r\n @SplitStartCharacters VARCHAR(255),\r\n @SplitEndCharacters VARCHAR(255),\r\n @ItemCharacters VARCHAR(255),\r\n @ii INT\r\n\r\n SELECT @HowMuchToDo = LEN(@string), @HowManyDone = 0,\r\n @StartOfSplit = 100, @SplitEndCharacters = '[a-z]',\r\n @SplitStartCharacters = COALESCE(@Delimiter,\r\n '[^-a-z'']'),\r\n @EndOfSplit = LEN(@SplitStartCharacters), @ii = 1\r\n\r\n WHILE @StartOfSplit > 0--we have a delimiter left to do\r\n AND @HowMuchToDo > 0--there is more of the string to split\r\n AND @ii <= COALESCE(@MaxSplit, @ii)\r\n BEGIN --find the delimiter or the start of the non-word block\r\n SELECT @StartOfSplit = PATINDEX('%' + @SplitStartCharacters + '%',\r\n RIGHT(@String,@HowMuchToDo) COLLATE Latin1_General_CI_AI) \r\n \r\n IF @StartOfSplit > 0--if there is a non-word block\r\n AND @delimiter IS NULL \r\n SELECT @EndOfSplit = --find the next word\r\n PATINDEX('%' + @SplitEndCharacters + '%',\r\n RIGHT(@string,@HowMuchToDo- @startOfSplit)\r\n COLLATE Latin1_General_CI_AI)\r\n \r\n IF @StartOfSplit > 0--if there is a non-word block or delimiter \r\n AND @ii < COALESCE(@MaxSplit, @ii + 1) --and there is a field\r\n --still to do\r\n INSERT INTO @Results (item)\r\n SELECT LEFT(RIGHT(@String, @HowMuchToDo),\r\n @startofsplit - 1)\r\n ELSE --if not then save the rest of the string\r\n INSERT INTO @Results (item)\r\n SELECT RIGHT(@String, @HowMuchToDo)\r\n \r\n SELECT @HowMuchToDo = @HowMuchToDo - @StartOfSplit\r\n - @endofSplit + 1, @ii = @ii + 1 \r\n END\r\n <\/pre>\n
SELECT @xml = (SELECT seqno, item\r\n FROM @results \r\n FOR\r\n XML PATH('element'),\r\n TYPE,\r\n ELEMENTS,\r\n ROOT('stringarray')\r\n )\r\n RETURN @xml\r\n END\r\n\r\n\r\nGO\r\n\r\nIF OBJECT_ID(N'ArrayToTable') IS NULL\r\n RAISERROR ('In order to try these examples, you will need the Array,\r\nArrayToTable, str_Replace, and Item functions from the TSQL String\r\nArray Workbench',16,1)\r\n<\/pre>\nSELECT * FROM dbo.ArrayToTable(dbo.split('If I wanted that c**p from you, \r\nI''d squeeze your head', NULL, NULL))\r\nSELECT dbo.split('How come you always program when drunk?\r\nBecause I learned how to when drunk', '?', NULL) \r\nSELECT dbo.split('This is the worst disaster to happen here since I arrived' \r\n ,NULL, NULL) \r\nSELECT * FROM dbo.ArrayToTable(dbo.split('When I read about\r\nService Broker, I find I have\r\namnesia and Deja vu at the same time\r\nI keep thinking I''ve forgotten\r\nit before', '\r\n', NULL))\r\n<\/pre>\nSplitLines<\/b> string Function<\/h3>\n
IF OBJECT_ID(N'SplitLines') IS NOT NULL\r\n DROP FUNCTION SplitLines\r\nGO\r\n<\/a>\r\nCREATE FUNCTION dbo.SplitLines\r\n(\r\n @String VARCHAR(8000),\r\n @keepends INT=0 \r\n)\r\nRETURNS XML\r\nAS BEGIN\r\nDECLARE @Delimiter VARCHAR(5)\r\nSELECT @Delimiter=CASE WHEN COALESCE(@keepends,0)<>0\r\nTHEN CHAR(13) ELSE '\r\n' END\r\nRETURN dbo.split(@string, @delimiter, NULL)\r\nEND\r\nGO\r\n\r\nSELECT * FROM ArrayToTable(dbo.SplitLines('\r\nWhen the guy who\r\nmade the first drawing board\r\ngot it wrong, what did\r\nhe go back to?\r\n',1))\r\n\r\nSELECT * FROM ArrayToTable(dbo.SplitLines('What is another\r\nword for ''Thesaurus''\r\n',0))\r\n-- seqno item\r\n-- ----------- ---------------------\r\n-- 1 What is another\r\n-- 2 word for 'Thesaurus'\r\n<\/pre>\n
DECLARE @tags VARCHAR(200) = 'Yan,Tyan,Tethera,Methera,Pimp,Sethera,Lethera,Hovera,Dovera,Dik,Yanadik,Tyanadik,Tethera dik,Methera dik,Bumfitt,Yanabumfit,Tyanabumfitt,Tetherabumfitt,Metherabumfitt,Giggot' \r\nSELECT value FROM STRING_SPLIT(@tags, ',') <\/pre>\n
within string Function<\/h3>\n
False<\/code>. Suffix can also be a list of substrings to look for. With the optional start parameter, the test should begin at that position. With the optional end,the test should stop comparing at that position. P.S. This isn’t a Python method, but it underpins the Startswith<\/code>, and EndsWith<\/code> routines. We add the contains<\/code> function to hide the wildcard.<\/p>\nIF OBJECT_ID(N'within') IS NOT NULL\r\n DROP FUNCTION within\r\nGO\r\n<\/a>CREATE FUNCTION within\r\n (\r\n @String VARCHAR(MAX),\r\n @Substring XML,\r\n @start INT = NULL,\r\n @end INT = NULL,\r\n @prefixWildcard VARCHAR(1)='%',\r\n @SuffixWildcard VARCHAR(1)='%'\r\n )\r\nRETURNS INT\r\nAS BEGIN\r\n DECLARE @Match INT\r\n SELECT @Start = COALESCE(@Start, 1),\r\n @End = COALESCE(@End, LEN(@String))\r\n IF @string IS NULL OR @Substring IS NULL\r\n RETURN NULL\r\n --convert a single Substring into an array of one. \r\n IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @Substring)) = 0\r\n SELECT @Substring = '<stringarray><element><seqno>1<\/seqno><item>'\r\n + CONVERT(VARCHAR(MAX),@Substring)\r\n + '<\/item><\/element><\/stringarray>'\r\n -- provide sensible defaults for the limiters\r\n SELECT @end = CASE WHEN @end > LEN(@string)\r\n THEN LEN(@string)\r\n ELSE @end\r\n END,\r\n @Start = CASE WHEN @start > LEN(@string)\r\n THEN LEN(@string)\r\n ELSE @start\r\n END\r\n--and it is one simple SELECT statement!\r\n SELECT @match= COUNT(*) FROM \r\n ( SELECT x.y.value('item[1]', 'VARCHAR(200)') AS [Substring ]\r\n FROM @Substring .nodes('\/\/stringarray\/element') AS x ( y )\r\n ) theSubstrings\r\n WHERE PATINDEX(@SuffixWildcard+SUBSTRING +@prefixWildcard,\r\n SUBSTRING(@string, @Start, @End - @start + 1))>0\r\nRETURN @match\r\n END\r\nGO\r\n\r\nSELECT dbo.within('I''m writing an unauthorised autobiography, but \r\nwhat I''ve always wanted to do is to write a book ending in the word\r\n''mayonnaise''',\r\n dbo.array('mayonnaise,thrifty,art,lust',','),\r\n DEFAULT, DEFAULT, DEFAULT, DEFAULT)\r\n-- 1\r\n<\/pre>\n
EndsWith<\/b> string Function<\/h3>\n
False<\/code>. The suffix can also be a list of suffixes to look for. With optional start, test string beginning at that position. With optional end, stop comparing string at that position.<\/p>\nIF OBJECT_ID(N'EndsWith') IS NOT NULL\r\n DROP FUNCTION EndsWith\r\nGO\r\n<\/a>CREATE FUNCTION dbo.EndsWith\r\n(\r\n @String VARCHAR(MAX),\r\n @prefix XML,\r\n @start INT = NULL,\r\n @end INT = NULL\r\n)\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN dbo.within(@String,@prefix,@start,@end,'','%')\r\nEND\r\nGO\r\n\r\nSELECT dbo.endswith('The IRA are indiscriminately killing men\r\nwomen and children, and now they''ve killed two Australians\r\nQuote from Margaret Thatcher', \r\n dbo.array('wilson,Reagan,Clinton,Thatcher',','),\r\n DEFAULT, DEFAULT)\r\nSELECT dbo.endswith(\r\n'If we don''t succeed, then we run the risk of failure\r\nQuote from Dan Quayle', 'Quayle', DEFAULT, DEFAULT)\r\n\r\nSELECT dbo.endswith(\r\n'Prudent dullness marked him out as project manager.', '[.;:,]',\r\n DEFAULT, DEFAULT)\r\n <\/pre>\n
StartsWith<\/b> string Function<\/h3>\n
False<\/code>. Prefix can also be a list of prefixes to look for. With optional start, test string beginning at that position. With optional end, stop comparing string at that position.<\/p>\nIF OBJECT_ID(N'StartsWith') IS NOT NULL\r\n DROP FUNCTION StartsWith\r\nGO\r\n<\/a>CREATE FUNCTION dbo.StartsWith\r\n(\r\n @String VARCHAR(MAX),\r\n @prefix XML,\r\n @start INT = NULL,\r\n @end INT = NULL\r\n)\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN dbo.within(@String,@prefix,@start,@end,'%','')\r\nEND\r\nGO\r\n\r\nSELECT dbo.StartsWith(\r\n'Aside from its purchasing power, money is pretty useless',\r\ndbo.array('power,money,love',','),27,DEFAULT)\r\n-- 1\r\n<\/pre>\n
Contains<\/b> string Function<\/h3>\n
IF OBJECT_ID(N'Contains') IS NOT NULL\r\n DROP FUNCTION [Contains]\r\nGO\r\n<\/a>CREATE FUNCTION dbo.[Contains]\r\n(\r\n @String VARCHAR(MAX),\r\n @substring XML,\r\n @start INT = NULL,\r\n @end INT = NULL\r\n)\r\nRETURNS INT\r\nAS BEGIN\r\n RETURN dbo.within(@String,@substring,@start,@end,'%','%')\r\nEND\r\nGO\r\nSELECT dbo.[contains]('What about coming to work for my company?\r\nWill that many people fit under a rock?','work',DEFAULT, DEFAULT)\r\n--1\r\n<\/pre>\n
Join<\/b> string Function<\/h3>\n
@separator<\/code> as separator:<\/p>\nIF OBJECT_ID(N'Join') IS NOT NULL\r\n DROP FUNCTION [Join]\r\nGO\r\n<\/a>CREATE FUNCTION dbo.[Join]\r\n(\r\n @array XML,\r\n @separator VARCHAR(MAX)\r\n)\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n DECLARE @joined VARCHAR(MAX)\r\n --it is conceivable that someone might use a string here, to\r\n --make sure it is XML in our format \r\n IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @array)) = 0\r\n SELECT @array = '<stringarray><element><seqno>1<\/seqno><item>'\r\n + CONVERT(VARCHAR(MAX), @array)\r\n + '<\/item><\/element><\/stringarray>'\r\n--and now once again it is a simple select statement\r\nSELECT @joined=COALESCE(@joined+@separator,'') + item FROM\r\n ( SELECT x.y.value('item[1]', 'VARCHAR(200)') AS [item],\r\n x.y.value('seqno[1]', 'INT') AS seqno\r\n FROM @array.nodes('\/\/stringarray\/element') AS x ( y )\r\n ) f\r\nORDER BY f.seqno\r\nRETURN @joined\r\nEND\r\nGO\r\nSELECT dbo.[join](dbo.array ('Waterp,Repr,Dispr,Al,L,R,Pr,',','),'oof,')\r\n-- Waterpoof,Reproof,Disproof,Aloof,Loof,Roof,Proof,\r\nSELECT dbo.[join](\r\n dbo.array ('F,r,i,e,d, ,E,g,g,s',','),'')\r\n-- Fried Eggs\r\n<\/pre>\n
Parts<\/b> string Function<\/h3>\n
sep<\/code>, and return an array containing the part before the separator, the separator itself, and the part after the separator. IF the separator is not found, return an array containing the string itself, followed by two empty strings. P.S. This is not part of the Python suite. It is used to support Partition<\/code> and RPartition<\/code>. Again, Phil required calming down before he knuckled down to write this, since he once swore he would never publish another string splitting routine.<\/p>\nIF OBJECT_ID(N'Parts') IS NOT NULL\r\n DROP FUNCTION Parts\r\nGO\r\n<\/a>CREATE FUNCTION dbo.Parts\r\n(\r\n @String VARCHAR(MAX),\r\n @sep VARCHAR(MAX),\r\n @Last INT=0 \r\n)\r\nRETURNS XML\r\nAS BEGIN\r\nDECLARE @SepPos INT,\r\n@XML AS XML\r\n\r\n DECLARE @results TABLE\r\n (\r\n seqno INT IDENTITY(1, 1),\r\n -- the sequence is meaningful here\r\n Item VARCHAR(MAX)\r\n )\r\nIF @last<>0\r\n SELECT @SepPos=dbo.rfind(@string,@sep,DEFAULT,DEFAULT)\r\nELSE\r\n SELECT @SepPos=CHARINDEX(@Sep,@string)\r\n\r\nIF @SepPos>0\r\nINSERT INTO @results(Item)\r\n SELECT LEFT(@String,@SepPos-1) \r\n UNION ALL SELECT @Sep\r\n UNION ALL SELECT RIGHT(@String,LEN(@String)-@Seppos-LEN(@sep)+1)\r\nELSE\r\nINSERT INTO @results(Item)\r\n SELECT @String\r\n UNION ALL SELECT ''\r\n UNION ALL SELECT ''\r\n SELECT @xml = (SELECT seqno, item\r\n FROM @results \r\n FOR\r\n XML PATH('element'),\r\n TYPE,\r\n ELEMENTS,\r\n ROOT('stringarray')\r\n )\r\n RETURN @xml\r\n END\r\nGO\r\n\r\nSELECT * FROM dbo.ArrayToTable(dbo.parts('IS your manager a bookworm? \r\nNO just an ordinary one','?',0))\r\nSELECT dbo.parts('None of my team ever made a fool of me. \r\nwell who was it then?','fool',0)\r\n<\/pre>\n
Partition <\/b>string Function<\/h3>\n
sep<\/code>, and return an array containing the part before the separator, the separator itself, and the part after the separator. If the separator is not found, return an array containing the string itself, followed by two empty strings.<\/p>\nIF OBJECT_ID(N'Partition') IS NOT NULL\r\n <\/a>DROP FUNCTION Partition\r\nGO\r\nCREATE FUNCTION dbo.Partition\r\n(\r\n @String VARCHAR(MAX),\r\n @Sep VARCHAR(MAX)\r\n)\r\nRETURNS XML\r\nAS BEGIN\r\n RETURN dbo.parts(@String,@sep,0)\r\nEND\r\nGO\r\n<\/pre>\n
RPartition<\/b> string Function<\/h3>\n