{"id":358,"date":"2008-03-16T00:00:00","date_gmt":"2008-03-16T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/tsql-string-array-workbench\/"},"modified":"2021-09-29T16:22:13","modified_gmt":"2021-09-29T16:22:13","slug":"tsql-string-array-workbench","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/tsql-string-array-workbench\/","title":{"rendered":"TSQL String Array Workbench"},"content":{"rendered":"

Contents<\/h2>\n
    \n
  1. Introduction<\/a><\/li>\n
  2. The array() function<\/a><\/li>\n
  3. The PHP-style str_Replace() function<\/a><\/li>\n
  4. The str_Find function<\/a><\/li>\n
  5. The str_GetDelimited function<\/a><\/li>\n<\/ol>\n

    Introduction<\/h2>\n

    Arrays aren’t difficult in SQL Server 2005. Here’s a very simple technique that can be extended to do some remarkably complex string processing.<\/p>\n

    A while back, a friend was bemoaning the poor string handling of SQL Server. He was a PHP programmer. There is, he told us, nothing like the array handling ability of PHP. Take the str_replace<\/code> function. So handy. It even takes arrays of strings so one can do quite complex string substitutions.<\/p>\n

    It got us thinking. We can do the same in SQL Server 2005 perfectly easily. It is perfectly possible to do arrays in SQL 2000, though with a bit more of a hack. If we get stuck into using XML than we can pass structures around between procedures and functions, as well as arrays.<\/p>\n

    Take the PHP example…<\/p>\n

    \/\/ Provides: You should eat pizza, beer, and ice cream every day\r\n$phrase  = \"You should eat fruits, vegetables, and fiber every day.\";\r\n$healthy = array(\"fruits\", \"vegetables\", \"fiber\");\r\n$yummy   = array(\"pizza\", \"beer\", \"ice cream\");\r\n\r\n$newphrase = str_replace($healthy, $yummy, $phrase);\r\n<\/pre>\n
    Let’s convert this to its SQL Server equivalent…<\/p>\n
    -- Provides: You should eat pizza, beer, and ice cream every day\r\nDECLARE @phrase VARCHAR(MAX),\r\n   @Healthy XML,\r\n   @yummy XML,\r\n   @newPhrase VARCHAR(MAX)\r\nSELECT   @phrase = 'You should eat fruits, vegetables, and fiber every day.',\r\n         @healthy = dbo.array('fruits,vegetables,fiber', ','),--we choose a , delimiter\r\n         @yummy = dbo.array('pizza,beer,ice cream', ','),\r\n         @newphrase = dbo.str_replace(@healthy, @yummy, @phrase)\r\nSELECT   @NewPhrase\r\n\/* OK. I've used delimited lists and you need to provide a delimiter or use the default comma.\r\nIt won't work until we've defined a couple of functions.*\/\r\nGO\r\n<\/pre>\n
    The first thing we need is an array()<\/code> function.<\/p>\n
    –The Array() function<\/h2>\n
    Here is a simple function that turns a list into an XML fragment. We choose to standardise on a root of ‘stringarray’ and call each item an ‘element’ with a sequence number and the string itself.<\/p>\n
    e.g.<\/p>\n
        \r\nSELECT   dbo.array('tinker,tailor,soldier,sailor', ',')\r\n\/*\r\nthis gives...\r\n<stringarray>\r\n<element>\r\n<seqno>1<\/seqno>\r\n<item>tinker<\/item>\r\n<\/element>\r\n<element>\r\n<seqno>2<\/seqno>\r\n<item>tailor<\/item>\r\n<\/element>\r\n<element>\r\n<seqno>3<\/seqno>\r\n<item>soldier<\/item>\r\n<\/element>\r\n<element>\r\n<seqno>4<\/seqno>\r\n<item>sailor<\/item>\r\n<\/element>\r\n<\/stringarray>\r\n*\/\r\nIF OBJECT_ID(N'array') IS NOT NULL\r\n   DROP FUNCTION array\r\nGO\r\nCREATE FUNCTION [dbo].[array]\r\n-- =================================================\r\n-- array Function\r\n-- =================================================\r\n-- This function returns an XML version of a list with\r\n-- the sequence number and the value of each element\r\n-- as an XML fragment\r\n-- Parameters\r\n-- array() takes a varchar(max) list with whatever delimiter you wish. The\r\n-- second value is the delimiter\r\n   (\r\n    @StringArray VARCHAR(8000),\r\n    @Delimiter VARCHAR(10) = ','\r\n    \r\n   )\r\nRETURNS XML\r\nAS BEGIN\r\n      DECLARE @results TABLE\r\n         (\r\n           seqno INT IDENTITY(1, 1),-- the sequence is meaningful here\r\n           Item VARCHAR(MAX)\r\n         )\r\n      DECLARE @Next INT\r\n      DECLARE @lenStringArray INT\r\n      DECLARE @lenDelimiter INT\r\n      DECLARE @ii INT\r\n      DECLARE @xml XML\r\n\r\n      SELECT   @ii = 0, @lenStringArray = LEN(REPLACE(@StringArray, ' ', '|')),\r\n               @lenDelimiter = LEN(REPLACE(@Delimiter, ' ', '|'))\r\n\r\n      WHILE @ii <= @lenStringArray + 1--while there is another list element\r\n         BEGIN\r\n            SELECT   @next = CHARINDEX(@Delimiter, @StringArray + @Delimiter,\r\n                                       @ii)\r\n             INSERT   INTO @Results\r\n                     (Item)\r\n                     SELECT   SUBSTRING(@StringArray, @ii, @Next - @ii)\r\n             SELECT   @ii = @Next + @lenDelimiter\r\n         END    \r\n      SELECT   @xml = ( SELECT seqno,\r\n                             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\nGO\r\n<\/pre>\n
    We now have a simple way of getting an ordered array. You can, of course, return a single element from an array:<\/p>\n
    DECLARE @seqno INT\r\nSELECT   @seqno = 4 --lets ask for element no. 4\r\nDECLARE @array XML\r\n\r\nSELECT   @array = dbo.array('one,two,three,four,five,six,seven,eight,nine,ten' ,\r\n                           ',')\r\n--now return the fourth one\r\nSELECT   @array.query('\r\n   for $ARRAY in \/stringarray\/element\r\nwhere $ARRAY\/seqno = sql:variable(\"@seqno\")  \r\n   return\r\n     <element>\r\n      { $ARRAY\/item }\r\n     <\/element>\r\n') AS SingleElement\r\n\/* returns\r\n<element>\r\n  <item>four<\/item>\r\n<\/element>\r\n<\/pre>\n
    And you can very easily turn it into a conventional SQL table:<\/p>\n
    SELECT   x.y.value('item[1]', 'VARCHAR(200)') AS [item],\r\n         x.y.value('seqno[1]', 'INT') AS [seqno]\r\nFROM     @array .nodes('\/\/stringarray\/element') AS x (y)\r\n\r\n--Though you might want to make it into an in-line function\r\nIF OBJECT_ID(N'ArrayToTable') IS NOT NULL\r\n   DROP FUNCTION ArrayToTable\r\nGO\r\n-- ================================================\r\n-- creates a table from an array created by dbo.array\r\n-- ================================================\r\nCREATE FUNCTION ArrayToTable\r\n(  \r\n@TheArray XML\r\n)\r\nRETURNS TABLE\r\nAS\r\nRETURN\r\n(\r\nSELECT   x.y.value('seqno[1]', 'INT') AS [seqno],\r\n        x.y.value('item[1]', 'VARCHAR(200)') AS [item]\r\nFROM     @TheArray.nodes('\/\/stringarray\/element') AS x (y)\r\n)\r\nGO\r\nSELECT * FROM dbo.ArrayToTable(dbo.array('Tiger tiger, my mistake|I thought that you were william blake','|'))\r\n\/*\r\nResult:\r\nseqno       item\r\n----------- ---------------------------------------\r\n1           Tiger tiger, my mistake\r\n2           I thought that you were william blake\r\n*\/\r\n\r\n--and you can get the number of elements in an array\r\nSELECT   dbo.array('one,two,three,four,five,six,seven,eight,nine,ten' ,\r\n                           ',').query('count(for $el in \/stringarray\/element\r\nreturn $el\/item)') AS ListCount\r\n--Result: 10\r\n\r\n--or just an XML list of all the items.\r\nSELECT   dbo.array('one,two,three,four,five,six,seven,eight,nine,ten' ,','\r\n).query('for $i in \/stringarray\/element return (\/stringarray\/element\/item)[$i]')\r\n\r\n\/* now getting an element from an array is simple once you know the XML magic spell. We prefer to wrap it in a function as XML is rather unforgiving *\/\r\n\r\nIF OBJECT_ID(N'item') IS NOT NULL \r\n   DROP FUNCTION item\r\nGO\r\n-- =================================================\r\n-- Gets the specified value of an item from an array\r\n-- =================================================\r\nCREATE FUNCTION dbo.item\r\n(\r\n@TheArray XML, @index INT \r\n\r\n)\r\nRETURNS VARCHAR(MAX)\r\nAS\r\nBEGIN\r\nRETURN (SELECT element.value('item[1]', 'VARCHAR(max)')\r\n    FROM @TheArray.nodes('\/stringarray\/element[position()=sql:variable(\"@index\")]') array(element))\r\n\r\nEND\r\nGO\r\nSELECT dbo.item(dbo.array('Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday', '|'),4)\r\n--Returns: Thursday\r\nDECLARE @months XML\r\nSELECT @Months=\r\n   dbo.array(\r\n'January,February,March,April,May,June,July,August,September,October,November,December',',')\r\n\r\nSELECT dbo.item(@Months,10)\r\n--Returns: October\r\n<\/pre>\n
    So we’re ready for some harder stuff! Here is the Str_Replace<\/code> function:<\/p>\n
    The PHP-Style str_Replace function<\/h2>\n
    IF OBJECT_ID(N'Str_Replace') IS NOT NULL\r\n   DROP FUNCTION Str_Replace\r\nGO\r\n-- =================================================\r\n-- Str_Replace Function\r\n-- =================================================\r\n-- This function returns a string or an array with all occurrences of search\r\n-- in subject replaced with the given replace value.\r\n\r\n-- Parameters\r\n-- str_replace() takes a value from each array and uses them to do search AND\r\n-- replace on subject . If replace has fewer values than search , then an empty\r\n-- string is used for the rest of replacement values. If search is an array and\r\n-- replace is a string, then this replacement string is used for every value\r\n-- of search .\r\n\r\n-- Their elements are processed first to last.\r\n-- This function returns a string with the replaced values.\r\nCREATE FUNCTION [dbo].[str_replace]\r\n   (\r\n    @Search XML,-- you can actually pass a string in this\r\n    @replace XML,-- and you can pass a string in this too\r\n    @Subject VARCHAR(MAX)\r\n   )\r\nRETURNS VARCHAR(MAX)\r\nAS BEGIN\r\n   --turn any simple strings into xml fragments with a single element\r\n      IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @Search)) = 0\r\n         SELECT   @Search = '<stringarray><element><seqno>1<\/seqno><item>'\r\n                 + CONVERT(VARCHAR(MAX), @search)\r\n                + '<\/item><\/element><\/stringarray>'\r\n      IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @Replace)) = 0\r\n         SELECT   @Replace = '<stringarray><element><seqno>1<\/seqno><item>'\r\n                 + CONVERT(VARCHAR(MAX), @Replace)\r\n                + '<\/item><\/element><\/stringarray>'\r\n      DECLARE @substitutions TABLE\r\n         (\r\n           [TheOrder] INT,\r\n           [FROM] VARCHAR(200),\r\n           [to] VARCHAR(200)\r\n         )\r\n      DECLARE @MaxTo VARCHAR(2000)\r\n   --because we want to allow fewer substitution values than search vaues\r\n   --as in the PHP version, it is a bit more complex.\r\n      INSERT   INTO @substitutions\r\n               ([TheOrder], [FROM], [to])\r\n               SELECT   f.Seqno, [from], [to]\r\n               FROM     ( SELECT    x.y.value('item[1]', 'VARCHAR(200)') AS [from],\r\n                                 x.y.value('seqno[1]', 'INT') AS seqno\r\n                       FROM      @Search.nodes('\/\/stringarray\/element') AS x ( y )\r\n                    ) f LEFT OUTER JOIN ( SELECT    x.y.value('item[1]',\r\n                                                           'VARCHAR(200)') AS [to],\r\n                                                 x.y.value('seqno[1]', 'INT') AS seqno\r\n                                       FROM      @Replace.nodes('\/\/stringarray\/element')\r\n                                                 AS x ( y )\r\n                                    ) g\r\n                         ON f.seqno = g.seqno\r\n   --first we want to get the last substitution value as a default.\r\n      SELECT   @Maxto = COALESCE([to], '')\r\n      FROM     @substitutions\r\n      WHERE    theOrder = ( SELECT MAX([TheOrder])\r\n                         FROM   @substitutions\r\n                         WHERE  [to] IS NOT NULL\r\n                       )\r\n   --and we get a nice set-based replacement query as a result\r\n      SELECT   @Subject = REPLACE(@Subject, [from], COALESCE([to], @Maxto))\r\n      FROM     @Substitutions\r\n   --neat, huh?\r\n      RETURN @Subject\r\n   END\r\nGO\r\n-- and now we have a simple test harness. (the real one goes on a bit!)\r\nDECLARE @from XML,\r\n   @to XML\r\nSELECT   @from = dbo.array('one,two,three,four', ',')\r\nSELECT   @to = dbo.array('five,six,seven,eight', ',')\r\nSELECT   dbo.str_replace(@from, @to,\r\n                         'One or two things I can''t abide, it is to see three or four busses in a row when one has been waiting one or two hours')\r\n--Result: five or six things I can't abide, it is to see seven or eight busses in a row when five has been waiting five or six hours\r\n\r\nSELECT   dbo.str_replace(dbo.array('%1,%2,%3', ','),\r\n             dbo.array('Aunt Edith|Splendid postcard of Devon|Cherish it all my life',\r\n             '|'), 'Dear %1,\r\nThank you so much for remembering my birthday by sending me  the %2.\r\nI shall %3. I trust you are well\r\nPhil')\r\n\/*\r\nResult:\r\nDear Aunt Edith,\r\nThank you so much for remembering my birthday by sending me the Splendid postcard of Devon.\r\nI shall Cherish it all my life. I trust you are well\r\nPhil*\/\r\n\r\n\r\nDECLARE @vowels XML\r\nSELECT   @vowels = dbo.array('a,e,i,o,u', ',')\r\nSELECT   OnlyConsonants = dbo.str_replace(@vowels, dbo.arraY('', ','),\r\n                                         'Hello World of SQL Server')\r\n--Result: Hll Wrld f SQL Srvr\r\n\r\n--now we check that strings work as well as arrays.\r\nSELECT   NoHello = dbo.str_replace('hello', 'goodbye',\r\n                                   'Hello World of SQL Server')\r\n--Result: goodbye World of SQL Server\r\n\r\n--demonstrating that there is no need for array variables now\r\nSELECT   Goodbye = dbo.str_replace(dbo.array('hello|SQL Server', '|'),\r\n                                   dbo.array('Goodbye|PHP', '|'),\r\n                                   'Hello World of SQL Server')\r\n--Result: Goodbye World of PHP\r\n\r\n--Order of replacement is important. We check that we got it right\r\nDECLARE @str VARCHAR(MAX),\r\n   @order XML,\r\n   @replace XML\r\nSELECT   @str = 'Line 1' + CHAR(13) + 'Line 2' + CHAR(10) + 'Line 3' + CHAR(13)\r\n        + CHAR(10) + 'Line 4' + CHAR(10),\r\n         @order = dbo.array(CHAR(13) + CHAR(10) + ',' + CHAR(10) + ','\r\n                           + CHAR(13), ','),\r\n         @replace = dbo.array('<br \/>', ',')\r\n-- Processes \\r\\n's first so they aren't converted twice.\r\nSELECT   dbo.str_replace(@order, @replace, @str) ;\r\n--Result: Line 1<br \/>Line 2<br \/>Line 3<br \/>Line 4<br \/>\r\n<\/pre>\n
    –The str_Find function<\/h2>\n
    IF OBJECT_ID(N'str_Find') IS NOT NULL\r\n   DROP FUNCTION str_Find\r\nGO\r\n--So a few more ideas for functions which you can pass arrays to\r\nCREATE FUNCTION [dbo].[str_Find]\r\n-- =================================================\r\n-- Str_Find Function\r\n-- =================================================\r\n-- This function returns an integer containing the number of  occurrences of\r\n-- @search in @subject.\r\n\r\n-- Parameters\r\n-- str_Find() takes a value from each array and uses them to do search\r\n-- on @subject\r\n-- This function returns an integer of the total count of the strings\r\n-- in @search found in @Subject.\r\n   (\r\n    @Search XML,-- you can actually pass a string in this\r\n    @Subject VARCHAR(MAX)\r\n   )\r\nRETURNS INT\r\nAS BEGIN\r\n      DECLARE @count INT\r\n      SELECT   @count = 0\r\n   --turn any simple strings into xml fragments with a single element\r\n      IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @Search)) = 0\r\n         SELECT   @Search = '<stringarray><element><seqno>1<\/seqno><item>'\r\n                 + CONVERT(VARCHAR(MAX), @search)\r\n                + '<\/item><\/element><\/stringarray>'\r\n      DECLARE @StringsTofind TABLE\r\n         (\r\n           [TheOrder] INT,\r\n           [whatToFind] VARCHAR(200)\r\n         )\r\n      INSERT   INTO @StringsTofind\r\n               ([TheOrder], [whatToFind])\r\n               SELECT   x.y.value('seqno[1]', 'INT') AS TheOrder,\r\n                         x.y.value('item[1]', 'VARCHAR(200)') AS [whatToFind]\r\n               FROM     @Search .nodes('\/\/stringarray\/element') AS x (y)\r\n\r\n      SELECT   @count = @count + ( LEN(@subject) - LEN(REPLACE(@Subject,\r\n                                                             [whatToFind], '')) )\r\n            \/ LEN(whatToFind)\r\n      FROM     @StringsTofind\r\n      RETURN @count\r\n   END\r\nGO\r\n--a few tests to show you how to use it!\r\nSELECT   dbo.str_find(dbo.array('Cialis,levitra,tramadol,casino,viagra,real-estate' ,\r\n                               ','),\r\n                     'Buy my wonderful Cialis. Cialis and viagra going cheap, and some real-estate too')\r\n--Result: 4\r\nSELECT   dbo.str_find('=', '============')\r\n--Result: 12\r\nDECLARE @search XML\r\nSELECT   @search = dbo.array('Bones!Brick Dust!Chalk!cement!Sugar', '!')\r\nSELECT   dbo.str_find(@search, 'Robyn Page is a wonderful programmer')\r\n--Result: 0\r\nDECLARE @string VARCHAR(80)\r\nSELECT   @String = 'a pinch  of sugar tastes better than a bowl of cement'\r\nSELECT   dbo.str_find(@search, @String)\r\n--Result: 2\r\nGO\r\n<\/pre>\n
    The str_GetDelimited function<\/h2>\n
    IF OBJECT_ID(N'str_GetDelimited') IS NOT NULL\r\n   DROP FUNCTION str_GetDelimited\r\nGO\r\nCREATE FUNCTION [dbo].[str_GetDelimited]\r\n\r\n-- =================================================\r\n-- str_GetDelimited Function\r\n-- =================================================\r\n-- This function returns a table of Strings taken from the string you\r\n-- pass to it. You can pass a number of alternative delimiters and it will\r\n-- pick them all up in one gulp.\r\n-- you also specify the offset, which is to say that you can opt to\r\n-- include all or part of the start delimiter in the string\r\n\r\n-- Parameters\r\n-- str_GetDelimited() takes a value from each array and uses them to\r\n-- find the delimiter\r\n-- This function returns a table of all the delimited strings found in\r\n-- @Search using any of the delimiters found in @StartDelimiter, and\r\n-- terminated by the delimiter in @EndDelimiter, using the offset in\r\n-- @offset\r\n-- If @EndDelimiter has fewer values than @StartDelimiter , then the last\r\n-- string is used for the rest of replacement @EndDelimiter. If @StartDelimiter is\r\n-- an array and @EndDelimiter is a string, then this @EndDelimiter string is used\r\n-- for every value of @StartDelimiter .  \r\n(\r\n    @StartDelimiter XML,-- you can actually pass a string in this\r\n    @EndDelimiter XML,-- you can actually pass a string in this\r\n    @offset XML,\r\n    @Subject VARCHAR(MAX)\r\n   )\r\nRETURNS @Strings TABLE\r\n   (\r\n    seqNo INT IDENTITY(1, 1),\r\n    String VARCHAR(255)\r\n   )\r\nAS BEGIN\r\n      DECLARE @LenSubject INT,\r\n         @ii INT,\r\n         @Start INT,\r\n         @Length INT,\r\n         @keywordLength INT,\r\n         @TheOrder INT,\r\n         @MaxEndDelimiter VARCHAR(2000),\r\n         @MaxOffset VARCHAR(2000)\r\n   --turn any simple strings into xml fragments with a single element\r\n      IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @StartDelimiter)) = 0\r\n         SELECT   @StartDelimiter = '<stringarray><eleme><seqno>1<\/seqno><item>'\r\n                 + CONVERT(VARCHAR(MAX), @StartDelimiter)\r\n                + '<\/item><\/element><\/stringarray>'\r\n      IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @EndDelimiter)) = 0\r\n         SELECT   @EndDelimiter = '<stringarray><element><seqno>1<\/seqno><item>'\r\n                 + CONVERT(VARCHAR(MAX), @EndDelimiter)\r\n                + '<\/item><\/element><\/stringarray>'\r\n      IF CHARINDEX('<stringarray>', CONVERT(VARCHAR(MAX), @offset)) = 0\r\n         SELECT   @offset = '<stringarray><element><seqno>1<\/seqno><item>'\r\n                 + CONVERT(VARCHAR(MAX), @offset)\r\n                + '<\/item><\/element><\/stringarray>'\r\n      DECLARE @substitutions TABLE\r\n         (\r\n           [TheOrder] INT,\r\n           [StartDelimiter] VARCHAR(200),\r\n           [EndDelimiter] VARCHAR(200),\r\n           offset INT\r\n         )\r\n      INSERT   INTO @substitutions\r\n               ([TheOrder], [StartDelimiter], [EndDelimiter], offset)\r\n               SELECT   f.Seqno, [StartDelimiter], [EndDelimiter], offset\r\n               FROM     ( SELECT    x.y.value('item[1]', 'VARCHAR(200)') AS [StartDelimiter],\r\n                                 x.y.value('seqno[1]', 'INT') AS seqno\r\n                       FROM      @StartDelimiter.nodes('\/\/stringarray\/element')\r\n                                 AS x ( y )\r\n                    ) f\r\n                     LEFT OUTER JOIN ( SELECT    x.y.value('item[1]',\r\n                                                           'VARCHAR(200)') AS [EndDelimiter],\r\n                                                 x.y.value('seqno[1]', 'INT') AS seqno\r\n                                       FROM      @EndDelimiter.nodes('\/\/stringarray\/element')\r\n                                                 AS x ( y )\r\n                                    ) g ON f.seqno = g.seqno\r\n                     LEFT OUTER JOIN ( SELECT    x.y.value('item[1]', 'INT') AS [offset],\r\n                                                 x.y.value('seqno[1]', 'INT') AS seqno\r\n                                       FROM      @offset.nodes('\/\/stringarray\/element')\r\n                                                 AS x ( y )\r\n                                    ) H\r\n                         ON f.seqno = h.seqno\r\n\r\n      SELECT   @MaxEndDelimiter = COALESCE([EndDelimiter], '')\r\n      FROM     @substitutions\r\n      WHERE    theOrder = ( SELECT MAX([TheOrder])\r\n                         FROM   @substitutions\r\n                         WHERE  [EndDelimiter] IS NOT NULL\r\n                       )\r\n      SELECT   @MaxOffset = COALESCE([offset], 0)\r\n      FROM     @substitutions\r\n      WHERE    theOrder = ( SELECT MAX([TheOrder])\r\n                         FROM   @substitutions\r\n                         WHERE  [offset] IS NOT NULL\r\n                       )\r\n\r\n--Get the length of the Subject and initialise things\r\n      SELECT   @LenSubject = LEN(REPLACE(@Subject, ' ', '|')),\r\n               @ii = @LenSubject\r\n      WHILE @ii > 0--find every delimited area in the Subject and put them\r\n          -- in a table\r\n         BEGIN--check for the next delimited area\r\n             SELECT   @start = 0\r\n             SELECT TOP 1\r\n                     @start = hit, @keywordLength = offset,\r\n                     @TheOrder = Theorder\r\n             FROM     (SELECT  [hit] = PATINDEX('%' + startDelimiter + '%',\r\n                                                 RIGHT(@Subject, @ii)),\r\n                               [offset] = COALESCE(offset, @MaxOffset),\r\n                               theOrder\r\n                       FROM    @substitutions\r\n                     ) f\r\n             WHERE    hit > 0\r\n             ORDER BY hit ASC, offset DESC\r\n\r\n            IF COALESCE(@start, 0) = 0\r\n               BREAK--no more?\r\n  --so we isolate the actual delimited string\r\n             SELECT   @Length = PATINDEX('%'\r\n                       + COALESCE(EndDelimiter, @MaxEndDelimiter) + '%',\r\n                             RIGHT(@Subject, @ii - @start - @keywordLength))\r\n             FROM     @substitutions\r\n             WHERE    theorder = @TheOrder\r\n             SELECT   @Length = CASE @length\r\n                                 WHEN 0 THEN @ii\r\n                                 ELSE @length\r\n                               END--no termination?\r\n             INSERT   INTO @strings\r\n                     (string) --add to our table\r\n                     SELECT   LEFT(SUBSTRING(RIGHT(@Subject, @ii),\r\n                                           @start + @keywordLength, @Length),\r\n                                 255)\r\n  --and reduce the length of the string we look at past the URL\r\n             SELECT   @ii = @ii - @start - @keywordLength - @Length\r\n         END\r\n      RETURN\r\n\r\n   END\r\n   GO\r\n        \r\n\r\nSELECT   *\r\nFROM     str_GetDelimited(\r\n                               dbo.array('<div>',','),\r\n                               dbo.array('<\/div>', ','),\r\n                               dbo.array('5',','),\r\n                               '<div>This is a div<\/div>')\r\n\/*\r\nResult:\r\nseqNo       String\r\n----------- --------------\r\n1           This is a div\r\n*\/\r\n--how about a way of examining URLs in messages?\r\nSELECT   *\r\nFROM     str_GetDelimited(\r\n   dbo.array('HREF=|HREF=\"|HREF=\" |HTTP:\/\/|HTTPS:\/\/|mailto:\/\/', '|'),\r\n    dbo.array('[\"> ]',','),--stop at a space, a > or a \"\r\n   dbo.Array('5,6,7,0',','),--all the last ones are zero offset as we want the start\r\n   'This is some spam <a HREF=www.Pinvoke.com> buy from us at HREF=\"www.Simple-Talk.com\" <\/a>and you can also buy from HREF=\" www.Red-Gate.com and I''ll sneak in a HTTP:\/\/www.SQLServerCentral.com and a mailto:\/\/phil@factor.com ')\r\n\r\n\/*\r\nseqNo       String\r\n----------- ---------------------------------\r\n1           www.Pinvoke.com\r\n2           www.Simple-Talk.com\r\n3           www.Red-Gate.com\r\n4           HTTP:\/\/www.SQLServerCentral.com\r\n5           mailto:\/\/phil@factor.com\r\n\r\n<\/pre>\n
    So there we have it. Phil and I hope that we’ve given you enough to get you started. There is a lot we’ve left out as the article would have gotten rather long. We also feel slightly guilty that we have left the SQL 2000 users out of this workshop, but you can do a surprising amount of this in SQL Server 2000 just with some simple string splitting techniques (We’ve covered the basics in a previous workbench). Perhaps someone else will contribute a SQL Server 2000 version that uses Varchar (8000)s<\/code>.<\/p>\n
    To cover a complete array handling scheme, we should, perhaps, have included array element deletion, insertion and update, but this is all in the XML primers, the Workbench seemed to be getting rather long, and Phil gets grumpy when I do too much FLWOR in a workbench. He says it demoralises people!<\/p>\n
    Prettified with the Simple-Talk SQL Prettifier.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    Robyn and Phil show how to use XML-based arrays to make string handling easier in SQL Server 2005\/2008, and illustrate the techniques with some useful functions, one of which copies the PHP str_Replace function.…<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4851,4151,4252,4190,4460,4217],"coauthors":[6813,6814],"class_list":["post-358","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-code-examples","tag-sql-server","tag-t-sql-programming","tag-tsql","tag-workbench","tag-xml"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/358","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=358"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/358\/revisions"}],"predecessor-version":[{"id":77216,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/358\/revisions\/77216"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=358"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=358"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=358"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=358"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}