Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разделение данных одного поля по разным полям  [new]
Aleksey br
Member

Откуда:
Сообщений: 73
День добрый
Есть некий набор данных записанных через точку с запятой. И нужно их разделить по полям. Сложность в том, что количество данных в строках поля может быть разным.
то есть есть вот так:
COLUMN
1;2;3;4;5;6
2;4;5;6
1;3;5

а нужно хотя бы так:
COL1COL2COL3COL4COL5COL6
123456
2456
135

если рассписывать через substring\patindex\charindex..., как это делаю я - получится крайне много кода...
select substring(COLUMN,0,patindex('%,%',COLUMN)) COL1
	, substring(COLUMN,len(substring(COLUMN,0,patindex('%,%',COLUMN)))+1,CHARINDEX(',',COLUMN,patindex('%,%',COLUMN)+1)-len(substring(COLUMN,0,patindex('%,%',COLUMN)))) COL2
	/*и так далее по нарастающей...*/
from TBL1


Можно ли как-то облегчить работу с высчитом начального номера символа обрезки и длины вырезаеваемого выражения?
3 апр 17, 18:37    [20362373]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
invm
Member

Откуда: Москва
Сообщений: 9722
declare @t table (c varchar(100));

insert into @t
values
 ('1;2;3;4;5;6'),
 ('2;4;5;6'),
 ('1;3;5');

select
 c.n.value('v[1]', 'varchar(10)'),
 c.n.value('v[2]', 'varchar(10)'),
 c.n.value('v[3]', 'varchar(10)'),
 c.n.value('v[4]', 'varchar(10)'),
 c.n.value('v[5]', 'varchar(10)'),
 c.n.value('v[6]', 'varchar(10)')
from
 @t a cross apply
 (select cast('<row><v>' + replace(a.c, ';', '</v><v>') + '</v></row>' as xml)) b(x) cross apply
 b.x.nodes('row') c(n);
3 апр 17, 18:57    [20362475]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
aleks2
Guest
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[f_nStrToTableEx](@str nvarchar(4000), @delimiter nvarchar(64)=',')
returns table as
return(
WITH 
  l as (select ld = LEN(N'|' + @delimiter + N'|') - 2, [s+d] = @str + @delimiter + N'|', ls = len(@str) )
, str_nums ( n1, n2, Number ) AS 
                                ( 
                                select  1 - ld as n1, charindex( @delimiter, [s+d] ) as n2, 0 as Number from l where @str is not null
                                UNION ALL 
                                select n2 as n1, charindex (@delimiter, [s+d], n2 + ld ) as n2, Number + 1 as Number
                                from str_nums cross join l
                                WHERE n2 < ls
                                ) 
select substring( @str, n1 + ld, n2 - n1 - ld ) as Value, Number, n1 + 1 as StartPosition FROM str_nums cross join l
)
3 апр 17, 18:58    [20362479]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
aleks2
Guest
а в 2016-м split() ё.
3 апр 17, 18:59    [20362486]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
buven
Member

Откуда:
Сообщений: 792
Aleksey br,

Если задача разовая - может в csv и обратно?
А так вот что нагуглилось на подумать вам.
3 апр 17, 19:01    [20362496]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
Aleksey br
Member

Откуда:
Сообщений: 73
invm,

Спасибо - очень помогло и полностью решает эту проблему!
3 апр 17, 20:42    [20362789]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
Aleksey br
Member

Откуда:
Сообщений: 73
invm,

А можно ли структурировать выводимый результат по определенным признакам, чтобы получать
COL1COL2COL3COL4COL5COL6
123456
2456
135
3 апр 17, 21:57    [20363035]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
invm
Member

Откуда: Москва
Сообщений: 9722
Aleksey br
чтобы получать
COL1COL2COL3COL4COL5COL6
123456
2456
135
select
 c.n.value('v[text() = "1"][1]', 'varchar(10)'),
 c.n.value('v[text() = "2"][1]', 'varchar(10)'),
 c.n.value('v[text() = "3"][1]', 'varchar(10)'),
 c.n.value('v[text() = "4"][1]', 'varchar(10)'),
 c.n.value('v[text() = "5"][1]', 'varchar(10)'),
 c.n.value('v[text() = "6"][1]', 'varchar(10)')
from
 @t a cross apply
 (select cast('<row><v>' + replace(a.c, ';', '</v><v>') + '</v></row>' as xml)) b(x) cross apply
 b.x.nodes('row') c(n);
3 апр 17, 22:26    [20363117]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
Aleksey br
Member

Откуда:
Сообщений: 73
invm,

пошерстил интернет - так и не понял: можно ли в данном контексте использовать опреатор LIKE в xQuery?
чтобы, к примеру, значения 22,2a,e2p попадали в поле COL2 согласно правилу like '%2%', ну а так же t3g и 5rty - в поля 3 и 5 соответственно. А если в значениях есть буквы и нет цыфр - выводить их в COL7?

declare @t table (c varchar(100));

insert into @t
values  ('16'), ('1as6'),  ('sd');

  select c from  @t
  where c like '%[a-z]%' and c not like '%[0-9]%' 


COL1COL2COL3COL4COL5COL6COL7
12a345rty6
e2p456sd
22t3g5
4 апр 17, 10:10    [20363873]     Ответить | Цитировать Сообщить модератору
 Re: Разделение данных одного поля по разным полям  [new]
invm
Member

Откуда: Москва
Сообщений: 9722
Aleksey br
можно ли в данном контексте использовать опреатор LIKE в xQuery?
Нельзя. Like к XQuery вообще никак не относится.
Можно так:
select
 e.c1, e.c2, e.c3, e.c4, e.c5, e.c6
from
 @t a cross apply
 (select cast('<row><v>' + replace(a.c, ';', '</v><v>') + '</v></row>' as xml)) b(x) cross apply
 (
  select
   max(case when d.v like '1' then d.v end) as c1,
   max(case when d.v like '2' then d.v end) as c2,
   max(case when d.v like '3' then d.v end) as c3,
   max(case when d.v like '4' then d.v end) as c4,
   max(case when d.v like '5' then d.v end) as c5,
   max(case when d.v like '6' then d.v end) as c6
  from
   (select c.n.value('.', 'varchar(10)') from b.x.nodes('row/v') c(n)) d(v)
 ) e;
4 апр 17, 11:19    [20364278]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить