Блог

У меня есть эта строка, например Green/1051;Brown/1258;Red/1110;Yellow /1024;Red/1147;

И я хотел бы иметь возможность получить следующую строку из этого 1051;1258;1110;1024;1147;

Я пробовал несколько решений, но они, как правило, обслуживают одну «часть» строки. Если это невозможно, я могу отформатировать это на стороне c #, однако было бы неплохо иметь возможность делать это в моей хранимой процедуре.

Спасибо

РЕДАКТИРОВАТЬ: К сожалению, это сторонний набор данных, поэтому я отформатировал эти данные с помощью C #. Из приведенных ниже ответов я согласен с тем, что SQL не предназначен для извлечения и анализа того, что мне нужно, его просто было бы проще поддерживать.

Я предполагаю, что причина, по которой данные должны быть проанализированы на сервере, заключается в их использовании в большем запросе. В противном случае нет причин анализировать его на сервере. Было бы намного проще сделать это на C #, например, с помощью регулярного выражения.

Не зная, какой это SQL Server, я просто предположу, что это 2016 или более поздняя версия, потому что это позволяет нам преобразовать строку в значение JSON, заменив на ":" и ; на "," . Аналогичный метод можно использовать в более старых версиях для преобразования строки в XML.

Предполагая, что эта простая таблица :

 declare @table table (id int identity primary key,col varchar(max))

insert into @table (col)
values ('Green/1051;Brown/1258;Red/1110;Yellow /1024;Red/1147;')
  

Мы можем использовать STUFF(col,len(col),1,'') для удаления конечной точки с запятой. Этот запрос :

 select stuff(col,len(col),1,'')
from @table
  

Дает

 Green/1051;Brown/1258;Red/1110;Yellow /1024;Red/1147
  

Замена / и ; с

 select replace(replace(stuff(col,len(col),1,''),'/','":"'),';','","')
from @table
  

Дает :

 Green":"1051","Brown":"1258","Red":"1110","Yellow ":"1024","Red":"1147
  

Теперь нам просто нужно заполнить строку JSON, окружив это значение {" символом и "} .

Как только мы это сделаем, мы можем использовать OPENJSON :

 select *
from @table 
    cross apply openjson('{"'   replace(replace(stuff(col,len(col),1,''),'/','":"'),';','","')   '"}')
  

Это вернет id col столбцы , из исходной таблицы и key , value и type столбцы из OPENJSON :

 select id,[key],value
from @table 
    cross apply openjson('{"'   
replace(replace(stuff(col,len(col),1,''),'/','":"'),';','","')   '"}') x
  

Вернет :

 id  key     value
1   Green   1051
1   Brown   1258
1   Red     1110
1   Yellow  1024
1   Red     1147
  

Мы можем объединить эти результаты с другой таблицей. Допустим, эти числа являются идентификаторами элементов, и у нас есть таблица инвентаризации с запасами для каждого из них :

 declare @inventory table (colorid int,amount int)

insert into @inventory (colorid,amount)
values
(1051,5),
(1258,10),
(1110,24),
(1024,2),
(1147,22)
  

Количество каких элементов превышает 20?

 select id, [key],colorid,amount
from @table 
    cross apply openjson('{"'   replace(replace(stuff(col,len(col),1,''),'/','":"'),';','","')   '"}') x
inner join @inventory i on i.colorid=x.value
where amount>20
  

Результат :

 id  key colorid amount
1   Red 1110    24
1   Red 1147    22
  

Найдите DelimitedSplit8K. Это позволит вам преобразовать строку в несколько строк, разделяющихся на ;

Затем вы можете использовать STUFF ..FOR XML для восстановления строки

В качестве альтернативы, если вы знаете, что вам всегда будут нужны ВСЕ числа и точки с запятой, и НИ ОДНА из букв и пробелов, которые вы могли бы использовать

 REPLACE(REPLACE(instring,[A-Z],''),' ','')