Блог

Главная - Вопросы по программированию - SQL Извлекает строку между двумя запятыми для заданной строки и разделяет в паре ключ-значение на два столбца

SQL Извлекает строку между двумя запятыми для заданной строки и разделяет в паре ключ-значение на два столбца

#sql #sql-server #sql-server-2008

#sql #sql-сервер #sql-server-2008

Вопрос:

Я объединил следующие значения в одном поле (я их разделил)

 --,rn  "Indie_163": "",
--,rn  "Pop_197": null,
--,rn  "Mgr_206": "Mark Timberland",
--,rn  "Date_225": "02/28/2019"
--,rn  "Fees_200": "57500",

Фактическое значение в поле:

  {"Indie_163": "","Pop_197": null,rn  "Mgr_206": "Mark Timberland",rn  "Date_225": "02/28/2019",rn  "Fees_200": "57500"})

Необходимо разделить их на 5 столбцов пары ключ-значение:

 Indie_Key | Indie_Val | Pop_Key | Pop_Val | Mgr_Key | Mgr_Val | Date_Key | Date_Val | Fees_Key | Fees_Val

Он должен заполнить ключи и значение как (Пробел «, null или значения, следующие после split (:) внутри двойных кавычек)

Примечание: Столбцы — ‘Indie_’, ‘Pop_’, ‘Mgr_’, ‘Date_’, ‘Fees_’ являются фиксированной строкой, номера суффиксов могут меняться

Комментарии:

1. Можете ли вы, пожалуйста, исправить форматирование? Кроме того, не могли бы вы, пожалуйста, указать в своем вопросе используемую вами СУБД: oracle, mysql, sql-server, …?

2. @GMB: Я не силен в форматировании в SOF (StackOverflow) : (

3. У вас действительно есть все эти экранированные кавычки ( " ) и возвраты каретки ( rn ) в значении поля?

4. @GMB: Они у меня есть

5. Что вы уже пробовали? Спрашивать о том, как что-то сделать, не показывая нам своих усилий, — это не то, о чем говорит SO

Ответ №1:

Ваши данные выглядят как плохо закодированное значение json. Вы можете исправить это с помощью replace(replace(x, '"', '"'), 'rn', '')

OPENJSON Функция T-SQL позволяет запрашивать значение в формате json. Дополнительная информация доступна на MSDN

Пусть у вас есть эти данные : 

 DECLARE @data TABLE (ID int, x nvarchar(max))
INSERT INTO @data
VALUES
    (1, ' {"Indie_163": "A","Pop_197": null,rn  "Mgr_206": "Mark Timberland",rn  "Date_225": "02/28/2019",rn  "Fees_200": "57500"})'),
    (2, ' {"Indie_163": "B","Pop_197": null,rn  "Mgr_206": "Mark X",rn  "Date_225": "02/28/2017",rn  "Fees_200": "57501"})'),
    (3, ' {"Indie_164": "C","Pop_197": "D",rn  "Mgr_206": "Mark Y",rn  "Date_225": "02/28/2018",rn  "Fees_200": "57502"})')

введите описание изображения здесь

Вы можете запросить их с помощью 

 SELECT * 
FROM @data
    CROSS APPLY openjson(replace(replace(x, '"', '"'), 'rn', '')) x

Что приведет к этому

введите описание изображения здесь

Затем вы можете запросить каждое нужное вам поле следующим образом : 

 SELECT id, 
    MAX(Pop_KEY)    POP_KEY,    MAX(Pop_VALUE)      Pop_VALUE,
    MAX(Indie_KEY)  Indie_KEY,  MAX(Indie_VALUE)    Indie_VALUE,
    MAX(Date_KEY)   Date_KEY,   MAX(Date_VALUE)     Date_VALUE,
    MAX(Fees_KEY)   Fees_KEY,   MAX(Fees_VALUE)     Fees_VALUE
FROM (
    SELECT id, 
        IIF(charindex('Pop',   [key]) = 1, [key],null) as Pop_KEY,   IIF( charindex('Pop',   [key]) = 1, [value],null) as Pop_VALUE,
        IIF(charindex('Indie', [key]) = 1, [key],null) as Indie_KEY, IIF( charindex('Indie', [key]) = 1, [value],null) as Indie_VALUE,
        IIF(charindex('Date',  [key]) = 1, [key],null) as Date_KEY,  IIF( charindex('Date',  [key]) = 1, [value],null) as Date_VALUE,
        IIF(charindex('Fees',  [key]) = 1, [key],null) as Fees_KEY,  IIF( charindex('Fees',  [key]) = 1, [value],null) as Fees_VALUE
    FROM @data
    CROSS APPLY openjson(replace(replace(x, '"', '"'), 'rn', '')) x 
) t
GROUP BY id

что приведет к

введите описание изображения здесь