REPLACE and SPLIT String in SQL and create Variables from Values

Question

I have a string like this in a database field:

"Anz Längsteiler":0;"Anz Querteiler":0;"BoxTyp":"M";"Führungslänge":500;"Gewicht":40;

In PowerShell I can do this:

$Hash = @{}
($BoxDS -replace '"','') -split ';' | foreach { 
       $Val = $_ -split ":" ; $Hash[$Val[0]] = $Val[1] 
}

to get an output like this for further use:

PS C:\Windows\System32\WindowsPowerShell\v1.0> $hash

Name                           Value
----                           ----- 
BoxTyp                         M
Anz Längsteiler                0
Anz Querteiler                 0
Führungslänge                  500
Gewicht                        40

Now I want to get rid of my PowerShell Script and create an SQL function.

Is there any way I can create a variable with the Name (as you can see in PowerShell output name column) and the value saved in there?

I'm very new to SQL and I read about REPLACE() and SPLIT() but can't really get my head around it. especially when it comes to dynamically creating the variables.

I'm on MS SQL Server 2016

What's your dbms?

D-Shih
– D-Shih

2018-09-03 08:34:19 +00:00
Commented Sep 3, 2018 at 8:34 — D-Shih
– D-Shih, Commented Sep 3, 2018 at 8:34
@D-Shih just edited in, MSSQL 2016

SimonS
– SimonS

2018-09-03 08:35:03 +00:00
Commented Sep 3, 2018 at 8:35 — SimonS
– SimonS, Commented Sep 3, 2018 at 8:35

D-Shih · Accepted Answer · 2018-09-03 08:49:12Z

2

If your sql-server version is 2016, you can try to use STRING_SPLITE function with CROSS APPLY.

I would write a subquery with STRING_SPLIT to split the ; to be a result set.

Splits the character expression using specified separator.

then write another subquery query to split the : by CROSS APPLY base on the upper result set.

Query 1:

SELECT  MAX(CASE WHEN t2.rn = 1 THEN t2.value end) Name,
        MAX(CASE WHEN t2.rn = 2 THEN t2.value end) Value
FROM (
  SELECT value as val
  FROM STRING_SPLIT(Replace('"Anz Längsteiler":0;"Anz Querteiler":0;"BoxTyp":"M";"Führungslänge":500;"Gewicht":40;','"',''),';')
) t1 CROSS APPLY (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY t1.val order by t1.val) rn
  FROM STRING_SPLIT(t1.val,':') 
) t2
where t1.val<>''
GROUP BY t1.val

Results:

|            Name | Value |
|-----------------|-------|
| Anz Längsteiler |     0 |
|  Anz Querteiler |     0 |
|          BoxTyp |     M |
|   Führungslänge |   500 |
|         Gewicht |    40 |

edited Sep 3, 2018 at 8:49

answered Sep 3, 2018 at 8:42

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SimonS Over a year ago

ok, thanks a lot. As I see your solution here, I know now that I need to learn a LOT to really do this in SQL. But this is a very good strating point for me.

SimonS Over a year ago

just a little thing - it does not get rid of the ", at least when I run it in SQLFiddle

D-Shih Over a year ago

@SimonS I edit my answer, just add Replace in the first subquery, before use STRING_SPLIT function.

Collectives™ on Stack Overflow

REPLACE and SPLIT String in SQL and create Variables from Values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related