CODEWITHSUNDEEP
sqlsql-servert-sql
Digital World
Digital World

Reputation: 9

SQL Server : how to select data between quotes (")

I have the following data in one column:

a:5:{i:1;s:1:"2";i:2;s:39:"Customer Name";i:3;s:41:"Occupation";i:4;s:9:"Extra Data";i:5;s:16:"Extra data";}

How can I select all data separated?

From the above example I need the following result:

Data1 Data2          Data3       Data4        Data5
2     Customer Name  Occupation  Extra Data   Extra Data

Thanks in advance,

Sakis

Upvotes: 0

Views: 148

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7928

This will be much more efficient than the XML approach with a profoundly less complicated execution plan: 

DECLARE @str nvarchar(max) = 'a:5:{i:1;s:1:"2";i:2;s:39:"Customer Name";i:3;s:41:"Occupation";i:4;s:9:"Extra Data";i:5;s:16:"Extra data";}';

SELECT 
  data1 = SUBSTRING([str], s1.d+1, s2.d  - (s1.d+1)),
  data2 = SUBSTRING([str], s3.d+1, s4.d  - (s3.d+1)),
  data3 = SUBSTRING([str], s5.d+1, s6.d  - (s5.d+1)),
  data4 = SUBSTRING([str], s7.d+1, s8.d  - (s7.d+1)),
  data3 = SUBSTRING([str], s9.d+1, s10.d - (s9.d+1))
FROM (VALUES (@str,CHARINDEX('"',@str))) s1([str],d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s1.d+1))) s2(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s2.d+1))) s3(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s3.d+1))) s4(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s4.d+1))) s5(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s5.d+1))) s6(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s6.d+1))) s7(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s7.d+1))) s8(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s8.d+1))) s9(d)
CROSS APPLY (VALUES (CHARINDEX('"',[str],s9.d+1))) s10(d);

Note that both approaches will only work provided that there are 10 quotes in the string. Both will work if there are more quotes.

Upvotes: 0

gofr1
gofr1

Reputation: 15987

You can use XML conversion:

DECLARE @str nvarchar(max) = 'a:5:{i:1;s:1:"2";i:2;s:39:"Customer Name";i:3;s:41:"Occupation";i:4;s:9:"Extra Data";i:5;s:16:"Extra data";}',
        @x xml
SELECT @x = CAST('<n>'+REPLACE(REPLACE(@str,':"','</n><a>'),'";','</a><n>')+'</n>' as xml)

SELECT  t.c.value('a[1]','nvarchar(max)') as [Data1],
        t.c.value('a[2]','nvarchar(max)') as [Data2],
        t.c.value('a[3]','nvarchar(max)') as [Data3],
        t.c.value('a[4]','nvarchar(max)') as [Data4],
        t.c.value('a[5]','nvarchar(max)') as [Data5]
FROM @x.nodes('/') as t(c)

Output:

Data1 Data2         Data3      Data4      Data5      
----- ------------- ---------- ---------- ---------- 
2     Customer Name Occupation Extra Data Extra data 

(1 row(s) returned)

After converting into XML you will get this:

<n>a:5:{i:1;s:1</n>
<a>2</a>
<n>i:2;s:39</n>
<a>Customer Name</a>
<n>i:3;s:41</n>
<a>Occupation</a>
<n>i:4;s:9</n>
<a>Extra Data</a>
<n>i:5;s:16</n>
<a>Extra data</a>
<n>}</n>

Upvotes: 2

Related Questions