разделен низ в колона

Имам данни, които идват от йерархична база данни и често има колони, които съдържат данни, които ТРЯБВА да бъдат в друга таблица, ако оригиналната база данни е била релационна.

Данните на колоната са форматирани по двойки, с LABEL\VALUE с интервал като разделител, както следва:

LABEL1\СТОЙНОСТ LABEL2\СТОЙНОСТ LABEL3\СТОЙНОСТ

Рядко има повече от един чифт в запис, но има до три. Има 24 различни възможни етикета. В тази таблица има други колони, включително ID. Успях да конвертирам тази колона в разреден масив, без да използвам курсор, с колони за ID, LABEL1, LABEL2 и т.н....

Но това не е идеално за използване в друга заявка. Другата ми опция е да използвам курсор, да прегледам цялата таблица веднъж и да пиша във временна таблица, но не мога да го накарам да работи както искам. Успях да го направя само за няколко минути във VB.NET, използвайки няколко вложени цикъла, но не мога да го направя в T-SQL дори с помощта на курсори. Проблемът е, че ще трябва да не забравям да стартирам тази програма всеки път, преди да искам да използвам таблицата, която създава. Не е идеален.

И така, прочетох ред, разделих двойките от „LABEL1\VALUE LABEL2\VALUE LABEL3\VALUE“ в масив, след това ги разделих отново, след което написах редовете

ID, LABEL1, VALUE

ID, LABEL2, VALUE

ID, LABEL3, VALUE

и т.н...

Осъзнавам, че „разделянето“ на низовете тук е трудната част за SQL, но просто изглежда много по-трудно, отколкото трябва да бъде. какво ми липсва


sql parsing sql-server tsql split
person David Griswold    schedule 20.01.2014    source източник
comment
Разгледайте този въпрос, може би това може да помогне stackoverflow.com/questions/21211605/   -  person M.Ali    schedule 21.01.2014
comment
FYI за всичко, което помогна, оказа се, че анализаторът е имал грешна информация за данните. Въпросната колона, която съдържаше информация за опаковки за продукти, не беше във формата, който смятаха.   -  person David Griswold    schedule 23.01.2014

Отговори (3)


arrow_upward
0
arrow_downward

Ако приемем, че етикетът с данни не съдържа . символи, можете да използвате проста функция за това:

CREATE FUNCTION [dbo].[SplitGriswold]
(
  @List   NVARCHAR(MAX),
  @Delim1 NCHAR(1),
  @Delim2 NCHAR(1)
)
RETURNS TABLE
AS
  RETURN
  ( 
    SELECT 
      Val1 = PARSENAME(Value,2),
      Val2 = PARSENAME(Value,1)
    FROM 
    (
      SELECT REPLACE(Value, @Delim2, '.') FROM
      ( 
        SELECT LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim1, @List + @Delim1, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim1 + @List, [Number], LEN(@Delim1)) = @Delim1
       ) AS y(Value)
     ) AS z(Value)
   );
GO

Примерна употреба:

DECLARE @x TABLE(ID INT, string VARCHAR(255));

INSERT @x VALUES
  (1, 'LABEL1\VALUE LABEL2\VALUE LABEL3\VALUE'),
  (2, 'LABEL1\VALUE2 LABEL2\VALUE2');

SELECT x.ID, t.val1, t.val2
FROM @x AS x CROSS APPLY 
 dbo.SplitGriswold(REPLACE(x.string, ' ', N'ŏ'), N'ŏ', '\') AS t;

(Използвах Unicode знак, който е малко вероятно да се появи в данните по-горе, само защото интервалът може да бъде проблематичен за неща като проверки на дължина. Ако има вероятност този знак да се появи, изберете друг.)

Резултати:

ID   val1       val2
--   --------   --------
1    LABEL1     VALUE
1    LABEL2     VALUE
1    LABEL3     VALUE
2    LABEL1     VALUE2
2    LABEL2     VALUE2

Ако вашите данни може да имат ., тогава можете просто да направите заявката малко по-сложна, без да променяте функцията, като добавите още един знак към микса, който е малко вероятно или невъзможно да бъде в данните:

DECLARE @x TABLE(ID INT, string VARCHAR(255));

INSERT @x VALUES
(1, 'LABEL1\VALUE.A LABEL2\VALUE.B LABEL3\VALUE.C'),
(2, 'LABEL1\VALUE2.A LABEL2.1\VALUE2.B');

SELECT x.ID, val1 = REPLACE(t.val1, N'ű', '.'), val2 = REPLACE(t.val2, N'ű', '.')
FROM @x AS x CROSS APPLY 
  dbo.SplitGriswold(REPLACE(REPLACE(x.string, ' ', 'ŏ'), '.', N'ű'), 'ŏ', '\') AS t;

Резултати:

ID   val1       val2
--   --------   --------
1    LABEL1     VALUE.A
1    LABEL2     VALUE.B
1    LABEL3     VALUE.C
2    LABEL1     VALUE2.A
2    LABEL2.1   VALUE2.B
person Aaron Bertrand    schedule 21.01.2014
comment
Това изглежда е пълно решение на моя въпрос. Успях просто да заменя x с моите данни от таблицата и да променя имената на полетата, за да съвпадат, и това работи за първи път. Няма възможни '.' в етикетите, така че първата заявка работи и не че трябва да има значение, но всички етикети са само два знака. - person David Griswold; 21.01.2014

arrow_upward
0
arrow_downward

Само с три стойности можете да успеете да направите това чрез груба сила:

select (case when rest like '% %'
             then left(rest, charindex(' ', rest) - 1)
             else rest
        end) as val2,
       (case when rest like '% %'
             then substring(col, charindex(' ', col) + 1, 1000)
        end) as val3
from (select (case when col like '% %'
                   then left(col, charindex(' ', col) - 1)
                   else col
              end) as val1,
             (case when col like '% %'
                   then substring(col, charindex(' ', col) + 1, 1000)
              end) as rest
      from t
     ) t
person Gordon Linoff    schedule 21.01.2014
comment
Това щеше да проработи, но трябваше да кажа, че в настоящите данни съм видял само три двойки етикет\стойност, но това не изключва да има повече в бъдеще. - person David Griswold; 21.01.2014
comment
@DavidGriswold. . . Въпросът казва, че има цели три. Дефинирана от потребителя функция или рекурсивен CTE са начините да направите това по-общо. - person Gordon Linoff; 21.01.2014
comment
Прав си, това казах. Бих гласувал за теб, ако можех :( - person David Griswold; 21.01.2014

arrow_upward
0
arrow_downward

Използване на SQL разделяне функция за низ, дадена в референтния урок за SQL, можете да разделите двойките етикет-стойност, както следва

SELECT
id, max(label) as label, max(value) as value
FROM (
SELECT 
    s.id, 
    label = case when t.id = 1 then t.val else NULL end,
    value = case when t.id = 2 then t.val else NULL end
FROM dbo.Split(N'LABEL1\VALUE1 LABEL2\VALUE2 LABEL3\VALUE3', ' ') s
CROSS APPLY dbo.Split(s.val, '\') t
) t
group by id

Можете да видите, че функцията за разделяне на низ се извиква два пъти, първо за разделяне на двойки от други. След това втората функция за разделяне, присъединена към предишната с помощта на CROSS APPLY, разделя етикетите от двойки

въведете описание на изображението тук

person Eralper    schedule 21.01.2014
comment
ID в моите данни не е за двойка етикет\стойност. може да има няколко двойки етикет\стойност за ID. - person David Griswold; 21.01.2014