0

I have a string with this format

"AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16"

and I would like to put the numbers in different columns, giving to each column the name you can find in the string before the =associated with this number.

So here, I would like to have 7 columns like that

AB AC1 AC2 AC3 AD1 AD2 AD3
10 11 12 13 14 15 16

Edit: This structure is fixed, and we'll always have one AB, three AC, and three AD.

My problem is that we have 3 occurrences of AC and 3 occurrences of AD, and I can't reach data contained in the 2nd and 3rd occurrence of AC and AD. I tried with PATINDEX and CHARINDEX and always reach only the first occurrence.

Is it possible to do it in a single SELECT query?

Jonathan
  • 97
  • 7
  • are these columns fixed or dynamic? For example if there was an additional AB=9 ... would there be columns AB1 and AB2 ? – John Cappelletti Jan 14 '21 at 00:49
  • 3
    SQL Server has recently introduced [`STRING_SPLIT`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15) function. – Alex Jan 14 '21 at 02:21
  • What is the SQL Server version? – Zhorov Jan 14 '21 at 07:03
  • @Zorov, it's SQL Server 2016 Management Studio (v 13.0.16106.4) – Jonathan Jan 14 '21 at 08:09
  • @JohnCappelletti Good question, I forgot to mention it and will edit the question: this structure is fixed, always one AB, three AC and three AD – Jonathan Jan 14 '21 at 08:11
  • @Alex Yes I know, I'm just trying to do it in a single request without using variables. Is is possible with STRING_SPLIT? – Jonathan Jan 14 '21 at 08:13

1 Answers1

1

You may try a JSON-based approach. You need to transform the input into a valid JSON with the appropriate structure ('AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16 into [{"AB":10},{"AC":11},{"AC":12},{"AC":13},{"AD":14},{"AD":15},{"AD":16}) and parse it with JSON_VALUE():

DECLARE @input nvarchar(max) = N'AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16'
SET @input = CONCAT('[{"', REPLACE(REPLACE(@input, ',', '"},{"'), '=', '":"'), '"}]')

SELECT 
   JSON_VALUE(@input, '$[0].AB') AS AB,
   JSON_VALUE(@input, '$[1].AC') AS AC1,
   JSON_VALUE(@input, '$[2].AC') AS AC2,
   JSON_VALUE(@input, '$[3].AC') AS AC3,
   JSON_VALUE(@input, '$[4].AD') AS AD1,
   JSON_VALUE(@input, '$[5].AD') AS AD2,
   JSON_VALUE(@input, '$[6].AD') AS AD3

Result:

AB  AC1 AC2 AC3 AD1 AD2 AD3
10  11  12  13  14  15  16

If the data is stored in a table:

SELECT 
   t.Input,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[0].AB') AS AB,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[1].AC') AS AC1,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[2].AC') AS AC2,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[3].AC') AS AC3,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[4].AD') AS AD1,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[5].AD') AS AD2,
   JSON_VALUE(CONCAT('[{"', REPLACE(REPLACE(t.Input, ',', '"},{"'), '=', '":"'), '"}]'), '$[6].AD') AS AD3
-- change the FROM clause with the real table and column names
FROM (VALUES (N'AB=10,AC=11,AC=12,AC=13,AD=14,AD=15,AD=16')) t (Input)
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • That's working really well!!! Thanks a lot! Just have to see if I can declare variables for my requests, because I'll use that on SSIS. But it should be great – Jonathan Jan 14 '21 at 08:43
  • Is this working if I need to get the original string from a table? I mean here you declare a variable and assign the string, but initially, I take this string from a column of the table and one to create 7 new columns based on the values of this column (strings with this format) – Jonathan Jan 14 '21 at 09:04
  • Your second answer is amazing! I'm really sorry to ask that, but how do you do this replacemenet with the table and the column. I do: SELECT MytableAlias.MyField, JSON..., JSON,....JSON, (replacing t.input by MytableAlias.MyField)... FROM MyTable as MyTableAlias. And it says : JSON text is not properly formatted. Unexpected character 's' is found at position 7 ... Do you see the problem? – Jonathan Jan 14 '21 at 18:48
  • Maybe my format problem is due to the fact that in my real srtrings, we don't have numbers (i just wanted to make a clear example) but strings ( little sentences in english), and maybe your format was done for numbers, right? – Jonathan Jan 14 '21 at 19:59
  • @Jonathan You are right. The answer assumes, that the values are numbers. Please, check the updated answer, but note that if the sentence contains JSON special characters, you'll need to escape them with `STRING_AGG()`. If you have problems, please post test data. Thanks.. – Zhorov Jan 14 '21 at 20:14
  • You don't even know how much you helped me. It's working nicely. I just have maybe one last question. I know I said it was never dynamic, but I just found in some records that it could be, mean we can have more than 3 AC and 3 AD, for instance one AB, four AC, and five AD (the maximum I could find is 6 AD. I guess it's a completely different problem. Or is it a small change? With a loop? – Jonathan Jan 14 '21 at 21:12
  • @Jonathan A dynamic statement is an option here, but yes, it's probably a good reason for another question. – Zhorov Jan 15 '21 at 06:43
  • You're right, and I asked another related question with this dynamic problem but in a more simple way than what I asked just above (I don't need such a complex thing). You're answer here helped me a lot. And the subsidiary question is there https://stackoverflow.com/questions/65734282/sql-server-how-to-find-the-substring-just-after-the-last-occurence-of-another-s :) Don't think it's too long to answer, if you could it would be so nice – Jonathan Jan 15 '21 at 10:23