10 0 MySQL Tip: Find the N-th element in a delimiter-separated string

10 Comments

• 
  Aka Guymelef more than 9 years ago *

  #1

  I wanted to see if I could do the same with Microsoft SQL Server. Unfortunately there is no equivalent to the behavior of the SUBSTRING_INDEX function. So I tried to write my own query even if I would recommend to create a dedicated function for better readability. This query is using CTE so it is only compatible with SQL Server 2005+.

  DECLARE @T VarChar(256)
  SET @T = 'http://totostaki.fr/page.html?param1=1&param2=22&param3=333&param4=4444'
  
  ; WITH SplitUrlParams
  AS (
    SELECT CAST(NULL AS VarChar(256)) AS Value
     , CHARINDEX('=', REVERSE(@T)) AS ValueDelimiterIdx
     , CHARINDEX('&', REVERSE(@T)) AS ParamDelimiterIdx
     , REVERSE(@T) AS RemainingValue
     , 0 AS InvertedOrder
    UNION ALL
    SELECT REVERSE(SUBSTRING(RemainingValue, 1, ValueDelimiterIdx - 1)) AS Value
     , CHARINDEX('=', SUBSTRING(RemainingValue, ParamDelimiterIdx + 1, LEN(RemainingValue))) AS ValueDelimiterIdx
     , CASE 
        WHEN CHARINDEX('&', SUBSTRING(RemainingValue, ParamDelimiterIdx + 1, LEN(RemainingValue))) > 0 THEN CHARINDEX('&', SUBSTRING(RemainingValue, ParamDelimiterIdx + 1, LEN(RemainingValue)))
        ELSE CHARINDEX('?', SUBSTRING(RemainingValue, ParamDelimiterIdx + 1, LEN(RemainingValue)))
       END
     AS ParamDelimiterIdx
     , SUBSTRING(RemainingValue, ParamDelimiterIdx + 1, LEN(RemainingValue)) AS RemainingValue
     , InvertedOrder + 1
    FROM SplitUrlParams
    WHERE ParamDelimiterIdx > 0
  )
  SELECT Value
  FROM SplitUrlParams
  WHERE InvertedOrder > 0
  ORDER BY InvertedOrder DESC

  • Line 1 : Here I'm using a variable but you could incorporate this query within a query to target columns.
  • Line 2 : This query works even if there is no url parameter or if the value is NULL.
  • Line 4 : I'm using a recursive CTE to process all url parameters.
  • Line 6, 9 : The first SELECT is the recursion initialization. The RemainingValue column is the remaining part of the url to process, on the initial state the url is reversed to be in a situation of value-first.
  • Line 7 : ValueDelimiterIdx gives the first position of the character '=' in RemainingValue.
  • Line 8 : ParamDelimiterIdx gives the first position of the character '&' in RemainingValue.
  • Line 10 : Since the url string is reversed, the first values extracted the last, this index will allow to reorder properly the values. Index 0 also denote the initial row which do not contains any value and must be ignored.
  • Line 14-17 : On the last chunk, the character '&' is not present anymore, in such occasion we need to search for the '?' character index.
  • Line 22 : The recursion is broken when the character '&' and '?' is not found in the remaining chunk. Meaning all parameters have been processed.
  • Line 26 : ignore the initialization row marked with InvertedOrder = 0 as it does not contains any value.
  • Line 27 : reverse the order of the rows to have the values in their original orders.

  Just to help the comprehension here is the raw results given for an url with four parameters :
  

  Value	ValueDelimiterIdx	ParamDelimiterIdx	RemainingValue	InvertedOrder
  NULL	5	12	4444=4marap&333=3marap&22=2marap&1=1marap?lmth.egap/rf.ikatsotot//:ptth	0
  4444	4	11	333=3marap&22=2marap&1=1marap?lmth.egap/rf.ikatsotot//:ptth	1
  333	3	10	22=2marap&1=1marap?lmth.egap/rf.ikatsotot//:ptth	2
  22	2	9	1=1marap?lmth.egap/rf.ikatsotot//:ptth	3
  1	0	0	lmth.egap/rf.ikatsotot//:ptth	4

• 
  Hypolite Petovan more than 9 years ago *

  #2

  Boy am I glad not to have to work with SQL Server. Thanks for trying though!

• 
  Aka Guymelef more than 9 years ago *

  #3

  Like I said if you factorize the logic in a function it became way simpler and easier to use.
  As a matter of fact, in a function I would not use the same algorithm because here the challenge is to do the job in one query/instruction while in a function I can use loop and such

• 
  Hypolite Petovan more than 9 years ago *

  #4

  Still, I prefer a one-liner over a SQL function any time of day!

• 
  Aka Guymelef more than 9 years ago *

  #5

  I'm not sure what you meant by that since in this case you used a (built-in) function to achieve your goals which SQL Server allows you to do the same once you created your user function.

  Even better you can create functions that will returns a table-like result rather than a scalar value. And if you're really hardcore you can create your function in a .NET DLL (using C# or VB.NET rather than T-SQL) and importing it in SQL Server to create CLR functions. Though I'm not using that because I'm a little concerned performance-wise and the importation requires high privileges that usually are not provided if you're using a shared hosting platform.

• 
  Hypolite Petovan more than 9 years ago *

  #6

  In that case, I needed a quick way to monitor a table being fed complete URLs. I didn't need to do any processing on those URL, so writing a complete SQL function would have been rather overkill.

• 
  Aka Guymelef more than 9 years ago *

  #7

  Experience proved to me that sometimes you should really spend the time to build debug/monitoring tools in order to save times in subsequent debugging/monitoring sessions. Especially if you found yourself repeating a lot of times those actions.

• 
  Hypolite Petovan more than 9 years ago *

  #8

  Wise words I can abide by, however in this particular case it was a one-time task and MySQL proved itself up to the challenge.

• 
  Aka Guymelef more than 9 years ago *

  #9

  Fair enough. Still that gave me some kind of challenge to make with SQL Server instead of working on that pain-in-the-ass project that come back every year for Christmas and on which I(we) should have coded some utility functions years ago. To which I, finally, decided to do this year.

• 
  Hypolite Petovan more than 9 years ago *

  #10

  Good for you! \o/