SQL Server: как выбрать данные в кавычках (")

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

У меня есть следующие данные в одном столбце:

 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";}

Как я могу выбрать все данные отдельно?

Из приведенного выше примера мне нужен следующий результат:

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

Заранее спасибо,

Сакис

1. фиксировано ли количество строк в кавычках?

2. Учитывая, что это сериализованные данные PHP, не лучше ли было бы расшифровать их в PHP? Или не сохранять их в базе данных таким образом в первую очередь?

Ответ №1:

Вы можете использовать преобразование XML:

 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)

Вывод:

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

(1 row(s) returned)

После преобразования в XML вы получите это:

 <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>

Ответ №2:

Это будет намного эффективнее, чем XML-подход, с гораздо менее сложным планом выполнения:

 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);

Обратите внимание, что оба подхода будут работать только при условии, что в строке 10 кавычек. Оба варианта будут работать, если кавычек будет больше.