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

Откуда:
Сообщений: 278
предлагаю очередной скрипт для парсинга строки
имеет смысл применять, если разделителей больше 1
легко расширяется на анализ несколько строк
извиняюсь за дизайн + может кто захочет улучшить

declare @mstr varchar(250)
declare @delim varchar(25) --- разделители
declare @tdelim table ( dc char(1) not null)

--- транспонируем разделители
select @delim = ',. ;:№!?%-'
insert into @tdelim ( dc)
select substring( @delim , v.number ,1)
from master.dbo.spt_values v
where v.number <= len(@delim) and v.number > 0 and v.type='P'

--- анализируемая строка
select @mstr = 'mama mila ramu 1234 ,; 4321 test - город майкоп привет! ура? finita молоко'

with WSP ( number , simvol, flag_slov ) as
( --- транспонируем строку и ставим признак слов 1 разделитель 0 --- граница слов - переход признака 01 и 10
select v.number, substring( @mstr , v.number ,1) ,
case
when d.dc is null then 1 else 0
end
from master.dbo.spt_values v
left join @tdelim d
on substring( @mstr , v.number ,1) = d.dc
where v.number <= len(@mstr) and v.number > 0 and v.type='P'
)
--- выделяем начала и окончания слов и связываем их по порядку
select start,finish,substring( @mstr , start , finish +1 - start ) as slovo
from
( select w1.number as start , row_number() over ( order by w1.number) as npp
from WSP as w1
left join WSP as w2
on w1.number = w2.number + 1
where w1.flag_slov = 1
and isnull(w2.flag_slov,0) = 0 ) s
,( select w1.number as finish , row_number() over ( order by w1.number) as npp
from WSP as w1
left join WSP as w2
on w1.number = w2.number - 1
where w1.flag_slov = 1
and isnull(w2.flag_slov,0) = 0 ) f
where f.npp = s.npp
5 ноя 13, 10:50    [15077205]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
aleks2
Guest
Это бред.
5 ноя 13, 12:49    [15078040]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
aleks2
Это бред.
Ну может у человека такая задача была в реале
6 ноя 13, 01:29    [15082131]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
хи-хи
Guest
SandalTree
aleks2
Это бред.
Ну может у человека такая задача была в реале

а то все остальные нереальные задачи решают

ТС, облом какой-то с вот такой строкой, ага?
select @mstr = 'mama mila ramu 1???234 ,; 4321 test - город майкоп привет! ура? finita молоко'
6 ноя 13, 02:26    [15082170]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
хи-хи
ТС, облом какой-то с вот такой строкой, ага?
select @mstr = 'mama mila ramu 1???234 ,; 4321 test - город майкоп привет! ура? finita молоко'

Почему облом? Вопросительный знак входит в список разделителей, на выходе получаются отдельные слова "1" и "234", что не так?
6 ноя 13, 03:01    [15082192]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
хи-хи
Guest
Гость333,
Вы честно запустили скрипт, ничего в нем не переправляя (не считая ";" перед with и новой тестовой строки)?

-------------------
start finish slovo
1 4 mama
6 9 mila
11 14 ramu
16 16 1

Msg 537, Level 16, State 2, Line 15
Invalid length parameter passed to the LEFT or SUBSTRING function.
6 ноя 13, 12:27    [15083712]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
хи-хи
Гость333,
Вы честно запустили скрипт, ничего в нем не переправляя (не считая ";" перед with и новой тестовой строки)?

Да.
Вот скрипт с вашей тестовой строкой, проверял на 2005, 2008R2 и 2012:
+
declare @mstr varchar(250)
declare @delim varchar(25) --- разделители
declare @tdelim table ( dc char(1) not null)

--- транспонируем разделители
select @delim = ',. ;:№!?%-'
insert into @tdelim ( dc)
select substring( @delim , v.number ,1)
 from master.dbo.spt_values v 
 where v.number <= len(@delim) and v.number > 0 and v.type='P' 

--- анализируемая строка 
select @mstr = 'mama mila ramu 1???234 ,; 4321 test - город майкоп привет! ура? finita молоко';

with WSP ( number , simvol, flag_slov ) as 
( --- транспонируем строку и ставим признак слов 1 разделитель 0 --- граница слов - переход признака 01 и 10
select v.number, substring( @mstr , v.number ,1) , 
 case 
 when d.dc is null then 1 else 0 
 end 
 from master.dbo.spt_values v 
 left join @tdelim d
 on substring( @mstr , v.number ,1) = d.dc
 where v.number <= len(@mstr) and v.number > 0 and v.type='P' 
) 
--- выделяем начала и окончания слов и связываем их по порядку
select start,finish,substring( @mstr , start , finish +1 - start ) as slovo 
 from
 ( select w1.number as start , row_number() over ( order by w1.number) as npp 
 from WSP as w1 
 left join WSP as w2
 on w1.number = w2.number + 1 
 where w1.flag_slov = 1
 and isnull(w2.flag_slov,0) = 0 ) s
 ,( select w1.number as finish , row_number() over ( order by w1.number) as npp 
 from WSP as w1 
 left join WSP as w2
 on w1.number = w2.number - 1 
 where w1.flag_slov = 1
 and isnull(w2.flag_slov,0) = 0 ) f 
 where f.npp = s.npp 

Вот результат, всё нормально разложилось:
+
start       finish      slovo
----------- ----------- -----------
1 4 mama
6 9 mila
11 14 ramu
16 16 1
20 22 234
27 30 4321
32 35 test
39 43 город
45 50 майкоп
52 57 привет
60 62 ура
65 70 finita
72 77 молоко

(13 row(s) affected)
6 ноя 13, 12:48    [15083936]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Glory
Member

Откуда:
Сообщений: 104751
Гость333
Вот результат, всё нормально разложилось:

Потому что коллейт кириллический ?
6 ноя 13, 12:56    [15084005]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
хи-хи
Guest
опа, Glory прав.
у меня и в исходном варианте вся кириллица вообще испарилась:
start finish slovo
1 4 mama
6 9 mila
11 14 ramu
16 19 1234
24 27 4321
29 32 test
62 67 finita

collation у меня SQL_Latin1_General_CP1_CI_AS,
но даже передав исходную строку юникодом, результат не меняется.
вчитываться и разгадывать сие лень!
6 ноя 13, 13:21    [15084193]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Glory
Гость333
Вот результат, всё нормально разложилось:

Потому что коллейт кириллический ?

Да.
На некириллическом коллейте в списке разделителей получаются два одинаковых символа (символ № преобразуется в вопросительный знак, который уже есть), из-за этого ошибка. Если убрать №, то всё работает. Защититься от дублей в строке разделителей можно, например, так:
declare @tdelim table ( dc char(1) not null primary key with(ignore_dup_key = on))
6 ноя 13, 13:22    [15084194]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
хи-хи
Guest
Гость333,

нифига оно не работает, даже если выкинуть № из разделителей!!!
кириллицу не возвращает абсолютно
6 ноя 13, 13:24    [15084209]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
хи-хи
но даже передав исходную строку юникодом, результат не меняется.

Попробуйте передать юникодом также строку разделителей, ну и везде заменить varchar/char на nvarchar/nchar.
6 ноя 13, 13:25    [15084215]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
хи-хи
кириллицу не возвращает абсолютно

Выполните на базе с некириллическим коллейтом такой запрос:
SELECT 'Хеллоу ворлд!'

У вас выведется набор вопросительных знаков. Ну а вопросительные знаки отбрасываются парсером, т.к. это разделители.
6 ноя 13, 13:28    [15084228]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
хи-хи
Guest
Гость333,

говорю же, мне (особенно сегодня) -- лень.
просто сообщаю ТС, что велосипед не едет.
свои задачи я таким "парсером" решать не собираюсь
(но, может, это Вы так ненавязчиво ТС-у подсказываете, чего дорабатывать )
6 ноя 13, 13:32    [15084248]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
я так понимаю если ТС это выложил - то у него это РАБОТАЕТ и он ЭТИМ РЕШИЛ свою задачу.
Поэтому я бы не стал тролить его за то что у кого-то при соответствющих условиях скрипт не работает так как он и не претендовал на некую универсальность.
6 ноя 13, 13:52    [15084377]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
хи-хи
Guest
Гость333
хи-хи
кириллицу не возвращает абсолютно

Выполните на базе с некириллическим коллейтом такой запрос:
SELECT 'Хеллоу ворлд!'

У вас выведется набор вопросительных знаков. Ну а вопросительные знаки отбрасываются парсером, т.к. это разделители.


спасибо.
не поверите, я в курсе про то, как "ляжет" кириллица, если ее в контексте базы
с некириллическим коллэйшеном запихать "как есть" (неюникодом, без "N") даже в _юникодное_ поле.
но такой сегодня вынос мозга идет, что не дошло, что вопросики выкинулись как разделители.
так что еще раз спасибо за разъяснение!!!
6 ноя 13, 13:54    [15084383]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
По мотивам этой темы и этой
+ Подготовка данных и участники тестирования
use tempdb;
go

if object_id('dbo.Numbers', 'U') is not null
 drop table dbo.Numbers;
go

create table dbo.Numbers (n int not null primary key);

insert into dbo.Numbers
select top (1000000)
 row_number() over (order by (select 1))
from
 master..spt_values a cross join
 master..spt_values b;
go

if object_id('dbo.[inline, recursive, charindex]', 'IF') is not null
 drop function dbo.[inline, recursive, charindex];
go

create function dbo.[inline, recursive, charindex]
(
 @Text nvarchar(max),
 @Delimiter nchar(1) = N','
)
returns table as
return (
 with p(v_id, v_start, v_stop) 
 as 
 (       
  select 1, cast(1 as bigint), charindex(@Delimiter, @Text)
 
  union all
 
  select
   v_id + 1, v_stop + 1, charindex(@Delimiter, @Text, v_stop + 1)
  from
   p
  where
   v_stop > 0       
 )     
select
 substring(@Text, v_start, isnull(nullif(v_stop, 0) - v_start, 9223372036854775807)) as Value,
 v_id 
from
 p
);
go

if object_id('dbo.[inline, recursive, patindex]', 'IF') is not null
 drop function dbo.[inline, recursive, patindex];
go

create function dbo.[inline, recursive, patindex]
(
 @Text nvarchar(max),
 @Delimiters nvarchar(30)
)
returns table
as
return (
 with x as
 (
  select
   1 as v_id, cast(1 as bigint) as v_start, patindex(b.p, a.s) as v_stop, substring(a.s, patindex(b.p, a.s) + 1, 99999) as v, b.p
  from
   (select @Text + left(@Delimiters, 1)) a(s) cross apply
   (select '%[' + @Delimiters +']%') b(p) 

  union all

  select
   v_id + 1, v_stop + 1, v_stop + patindex(p, v), substring(v, patindex(p, v) + 1, 9223372036854775807), p
  from
   x
  where
   patindex(p, v) > 0
 )
 select
  substring(@Text, v_start, v_stop - v_start) as Value,
  v_id
 from
  x
 where
  substring(@Text, v_start, v_stop - v_start) > ''
);
go

if object_id('dbo.[inline, table of numbers]', 'IF') is not null
 drop function dbo.[inline, table of numbers];
go

create function dbo.[inline, table of numbers]
(
 @Text nvarchar(max),
 @Delimiter nchar(1) = N','
)
returns table as
return (
 with t(s, l) as
 (select @Delimiter + @Text, len(@Delimiter + @Text)),
 x (v_start, v_len, s) as
 (
  select
   charindex(@Delimiter, t.s, n) + 1,
   isnull(nullif(charindex(@Delimiter, t.s, charindex(@Delimiter, t.s, n) + 1), 0) - charindex(@Delimiter, t.s, n) - 1, 9223372036854775807),
   t.s
  from
   t cross apply
   (select top (t.l) n from dbo.Numbers where n > 0 order by n) n
  where
   substring(t.s, n.n, 1) = @Delimiter
 )
 select
  substring(s, v_start, v_len) as Value,
  v_start as v_id
 from
  x
);
go

if object_id('dbo.[table-valued, cycle]', 'TF') is not null
 drop function dbo.[table-valued, cycle];
go

create function dbo.[table-valued, cycle]
(
  @Text nvarchar(max),
  @Delimiter nchar(1) = N','
)
returns @Elements table (Value nvarchar(max), v_id int) 
as
begin
 declare @s nvarchar(max), @v nvarchar(max), @p int, @pp int, @o int;
 
 select
  @pp = 1,
  @o = 1,
  @s = rtrim(ltrim(@Text)) + @Delimiter;
  
 set @p = charindex(@Delimiter, @s, @pp);
 
 while @p > 0
  begin
   set @v = substring(@s, @pp, @p - @pp);
   
   insert into @Elements
    (Value, v_id)
   values
    (@v, @o);

   set @o = @o + 1;
   
   set @pp = @p + 1;
   set @p = charindex(@Delimiter, @s, @pp);
  end;

 return;
end;
go

if object_id('dbo.[xml]', 'IF') is not null
 drop function dbo.[xml];
go

create function dbo.[xml]
(
 @Text nvarchar(max),
 @Delimiter nchar(1) = N','
)
returns table 
as
return (
 with x as
 (
  select cast('<v><![CDATA[' + replace(replace(@Text, ']]>', ']]]]><![CDATA[>'), @Delimiter, ']]></v><v><![CDATA[') + ']]></v>' as xml) v
 )
 select
  n.value('.', 'varchar(max)') as Value,
  row_number() over (order by (select 1)) as v_id
 from
  x cross apply
  x.v.nodes('v') t(n)
);
go

if object_id('dbo.[inline, transponse]', 'IF') is not null
 drop function dbo.[inline, transponse];
go

create function dbo.[inline, transponse]
(
 @Text nvarchar(max),
 @Delimiters nvarchar(30)
)
returns table as
return (
 with delim(dc) as
 (
  --- транспонируем разделители
  select substring( @Delimiters , v.n ,1)
  from dbo.Numbers v
  where datalength(@Delimiters) > 0 and v.n <= isnull(nullif(len(@Delimiters), 0), 1) and v.n > 0
 ),
 WSP ( number , simvol, flag_slov ) as
 ( --- транспонируем строку и ставим признак слов 1 разделитель 0 --- граница слов - переход признака 01 и 10
 select v.n, substring( @Text , v.n ,1) ,
 case
  when d.dc is null then 1 else 0
 end
 from dbo.Numbers v
 left join delim d
 on substring( @Text , v.n ,1) = d.dc
 where v.n <= len(@Text) and v.n > 0
 )
--- выделяем начала и окончания слов и связываем их по порядку
 select start as v_id,  substring( @Text , start , finish +1 - start ) as Value
 from
 ( select w1.number as start , row_number() over ( order by w1.number) as npp
 from WSP as w1
 left join WSP as w2
 on w1.number = w2.number + 1
 where w1.flag_slov = 1
 and isnull(w2.flag_slov,0) = 0 ) s
 ,( select w1.number as finish , row_number() over ( order by w1.number) as npp
 from WSP as w1
 left join WSP as w2
 on w1.number = w2.number - 1
 where w1.flag_slov = 1
 and isnull(w2.flag_slov,0) = 0 ) f
 where f.npp = s.npp 
);
go
+ Тест
use tempdb;
go

declare @d nvarchar(30) = ',', @s nvarchar(max), @c int = 10;

select
 @s = stuff((
  select top (@c) ',' + right(replicate('0', 10) + cast(n as nvarchar(30)), 10) from dbo.Numbers for xml path(''), type
 ).value('.', 'nvarchar(max)'), 1, 1, '');

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

select top (10000)
 n, @s as s
into
 #t
from
 dbo.Numbers
order by
 n;

set statistics time on;

print '[inline, recursive, charindex]';
select
 count_big(*)
from
 #t t cross apply
 dbo.[inline, recursive, charindex](t.s, @d)
option
 (maxrecursion 0);

print '[inline, recursive, patindex]';
select
 count_big(*)
from
 #t t cross apply
 dbo.[inline, recursive, patindex](t.s, @d)
where
 @c <= 100
option
 (maxrecursion 0);

print '[inline, table of numbers]';
select
 count_big(*)
from
 #t t cross apply
 dbo.[inline, table of numbers](t.s, @d)
option
 (maxrecursion 0);

print '[table-valued, cycle]';
select
 count_big(*)
from
 #t t cross apply
 dbo.[table-valued, cycle](t.s, @d)
option
 (maxrecursion 0);

print '[xml]';
select
 count_big(*)
from
 #t t cross apply
 dbo.[xml](t.s, @d)
option
 (maxrecursion 0);

print '[inline, transponse]';
select
 count_big(*)
from
 #t t cross apply
 dbo.[inline, transponse](t.s, @d)
option
 (maxrecursion 0);

set statistics time off;
go
Также в тест была включена простейшая SQLCLR-обертка для RegEx.Split

Результаты:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4276.0 (X64) 
Feb 8 2013 10:37:00
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

@c = 10
[CPU time][Elapsed time]
[inline; table of numbers]04
[xml]04
[RegEx.Split wrapper]06
[table-valued; cycle]3144
[inline; transponse]639596
[inline; recursive; charindex]967978
[inline; recursive; patindex]48984924

@c = 100
[CPU time][Elapsed time]
[xml]1620
[RegEx.Split wrapper]1521
[inline; table of numbers]3125
[table-valued; cycle]109106
[inline; transponse]21531841
[inline; recursive; charindex]89239249
[inline; recursive; patindex]6101261699

@c = 1000
[CPU time][Elapsed time]
[xml]172167
[RegEx.Split wrapper]156180
[inline; table of numbers]203288
[table-valued; cycle]702705
[inline; transponse]100636477
[inline; recursive; charindex]110230114859
[inline; recursive; patindex]-Ушел в себя и не вернулся
8 ноя 13, 13:18    [15097121]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Valer
Member

Откуда:
Сообщений: 278
invm, я сразу указал, что скрипт расчитан на много разделителей ( > 1) ,
для одного разделителя не было смысла дергаться,
+ это принципиально не функция ( при обработке нескольких строк надо протащить их id внутрь )
+ получение таблицы делимитеров из строки в табличку делается 1 раз
8 ноя 13, 14:34    [15097723]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Valer
+ это принципиально не функция ( при обработке нескольких строк надо протащить их id внутрь )
Про APPLY слышали?
Тело табличной inline функции - это в чистом виде SELECT,
который можно использовать в запросе и не прибегая к синтаксису функции.
8 ноя 13, 14:48    [15097867]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Valer
Member

Откуда:
Сообщений: 278
invm , к сожалению не могу воспроизвести тест на Microsoft SQL Server 2005

Сообщение 208, уровень 16, состояние 1, строка 1
Invalid object name 'dbo.Numbers'.
8 ноя 13, 17:14    [15098944]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Valer
Invalid object name 'dbo.Numbers'.

Numbers — это таблица натуральных чисел. Используется вместо вашего "master.dbo.spt_values where type = 'P'".
И показан же скрипт для её создания, видимо, вы что-то пропустили:
create table dbo.Numbers (n int not null primary key);

insert into dbo.Numbers
select top (1000000)
 row_number() over (order by (select 1))
from
 master..spt_values a cross join
 master..spt_values b;
go
8 ноя 13, 17:19    [15098980]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Valer
Member

Откуда:
Сообщений: 278
Граждане! Цель сообщения была в показе одного из вариантов парсинга строки на TSQL, вполне возможно этот метод не самый быстрый ( такой цели и не ставилось ), но многое зависит от данных и того что надо вытащить из них. Сам по себе парсинг не самоцель, да и делать его лучше на си. Возможно кому нибудь этот скрипт поможет или способ покажется любопытным. Предлагаю прекратить дальнейшее обсуждение темы в этом топике и мое обучение.
8 ноя 13, 19:12    [15099543]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Valer
Сам по себе парсинг не самоцель, да и делать его лучше на си.
Это почему это?
Valer
Предлагаю прекратить дальнейшее обсуждение темы в этом топике и мое обучение.
Только после сдачи ЕГЭ!
8 ноя 13, 19:32    [15099610]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
invm
Member

Откуда: Москва
Сообщений: 9719
Valer
Сам по себе парсинг не самоцель, да и делать его лучше на си
Очень странное утверждение.

Функция на основе вашего скрипта была включена в тест просто как один из вариантов. Кстати, это скрипт (и функция тоже) будет неверно работать, если пробел будет указан последним (или единственным) разделителем.
8 ноя 13, 20:36    [15099851]     Ответить | Цитировать Сообщить модератору
 Re: очередной скрипт для парсинга строк  [new]
Valer
Member

Откуда:
Сообщений: 278
jap, после сдачи создайте топик
invm, делать из скрипта функцию я вас не просил, тем более и сделали вы это не корректно
10 ноя 13, 19:18    [15105294]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить