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

Откуда:
Сообщений: 585
 		Коллеги!

Достался нам вот такой вот монстр программисткой мысли (см. код ниже).
Работает функция хорошо - замечаний нет (результат ее работы на скрине)

Вопрос такой:
объясните как это работает? )))))


ALTER FUNCTION [dbo].[SplitStrings]
(
   @List VARCHAR(MAX),
   @Delimiter VARCHAR(5)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL 
						SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
  FROM cteStart s;


К сообщению приложен файл. Размер - 7Kb
9 окт 17, 15:41    [20854972]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
 		RegisteredUser,

посимвольно :)
9 окт 17, 15:43    [20854988]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
o-o
Guest 		поджойнили строку с таблицей чисел
cteTally(N) от 0 до <длина строки>,
(т.е. развернули строку вертикально,
возле каждого номера соответствующий символ строки,
и джойним только по разделителю),
получили позиции разделителя cteStart.
ну все, теперь выгребаем подстроки от найденной позиции разделителя
до следующего разделителя
9 окт 17, 15:53    [20855022]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
 		Элементарно.
Первые 4 CTE - только для генерации таблицы с большим количеством строк.
cteStart(N1) получает позиции разделителя в строке и размещает эти числа в сгенерированной таблице в виде строк.
Основной запрос берёт эти позиции и ищет первый разделитель после cteStart(N1), выдаёт подстроку между этими позициями.

Однако, ISNULL(@List,1) - это просто чушь собачья.

Да! На форуме есть и другие аналогичные функции, и даже более универсальные (для набора символов-разделителей, например).
И решения этой задачи с помощью XML, и много чего ещё...
Стоит только поискать
9 окт 17, 15:54    [20855023]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
aleks222
Guest
RegisteredUser
Коллеги!

Достался нам вот такой вот монстр программисткой мысли (см. код ниже).
Работает функция хорошо - замечаний нет (результат ее работы на скрине)

Вопрос такой:
объясните как это работает? )))))


ALTER FUNCTION [dbo].[SplitStrings]
(
   @List VARCHAR(MAX),
   @Delimiter VARCHAR(5)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL 
						SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
  FROM cteStart s;


Редкостный бред.

То же самое, только поэффективней.
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
)
9 окт 17, 15:54    [20855026]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3653
 		п-ц, и даже пробелы не обрезали
9 окт 17, 16:26    [20855132]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
RegisteredUser
Member

Откуда:
Сообщений: 585
aleks222

Редкостный бред.

То же самое, только поэффективней.
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
)


на подобии этой у меня уже была функция.
меня поразил та с CTE
9 окт 17, 16:32    [20855157]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
RegisteredUser
Member

Откуда:
Сообщений: 585
iap

Да! На форуме есть и другие аналогичные функции, и даже более универсальные (для набора символов-разделителей, например).



Вот это сейчас как раз актуальненко.
Хотелось бы как-то расклепать строку вида

'dd-ff, ff-gg, yy-kk....'
-- где  ',' - разделитель для строк
--  а '-' - разделитель для полей
9 окт 17, 16:36    [20855171]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3653
RegisteredUser
iap
Да! На форуме есть и другие аналогичные функции, и даже более универсальные (для набора символов-разделителей, например).



Вот это сейчас как раз актуальненко.
Хотелось бы как-то расклепать строку вида

'dd-ff, ff-gg, yy-kk....'
-- где  ',' - разделитель для строк
--  а '-' - разделитель для полей


@@version ?
9 окт 17, 16:47    [20855214]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
RegisteredUser
Member

Откуда:
Сообщений: 585
Ролг Хупин
RegisteredUser
пропущено...


Вот это сейчас как раз актуальненко.
Хотелось бы как-то расклепать строку вида

'dd-ff, ff-gg, yy-kk....'
-- где  ',' - разделитель для строк
--  а '-' - разделитель для полей


@@version ?


Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) Jul 8 2017 16:43:40 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
9 окт 17, 16:49    [20855220]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
aleks222
Редкостный бред.

То же самое, только поэффективней.
Когда за дело берется всеми нами любимый гуру оптимизации и эффективности, обычно получается вот так:
+
use tempdb;
go

create FUNCTION [dbo].[Редкостный бред]
(
   @List NVARCHAR(4000),
   @Delimiter NVARCHAR(5)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL 
						SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
  FROM cteStart s;
go

create function [dbo].[То же самое, только поэффективней](@str nvarchar(4000), @delimiter nvarchar(5)=',')
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
)
go

create table dbo.t (id int identity primary key, s nvarchar(4000));
insert into dbo.t
select top (10000)
 substring(c.x.value('.', 'nvarchar(4000)'), 2, 4000)
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b cross apply
 (
  select top (cast(rand(checksum(newid()) + a.number) * 500 as int) + 1)
   ',' + name
  from
   master.dbo.spt_values
  for xml path(''), type
 ) c(x);
go

declare @c int;

set statistics time on;

select
 @c = count(*)
from
 dbo.t cross apply
 dbo.[Редкостный бред](t.s, ',') a
option
 (maxdop 1)

select
 @c = count(*)
from
 dbo.t cross apply
 dbo.[То же самое, только поэффективней](t.s, ',') a
option
 (maxdop 1, maxrecursion 0)

set statistics time off;
go

drop table dbo.t;
drop function dbo.[То же самое, только поэффективней], dbo.[Редкостный бред];
go

Редкостный бред43 ms.
То же самое, только поэффективней28257 ms.
9 окт 17, 17:13    [20855308]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
RegisteredUser
Хотелось бы как-то расклепать строку вида
И что должно в результате получится?
9 окт 17, 17:15    [20855317]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3653
invm
RegisteredUser
Хотелось бы как-то расклепать строку вида
И что должно в результате получится?


расклёпаная строка
9 окт 17, 17:37    [20855413]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2791
 		Вариант до кучи:
set ansi_nulls,quoted_identifier on;
go
create function tbl.char
 (
  @list nvarchar(max) = null,
  @sep  nchar(1)      = null
 )
  returns @tbl table(name nvarchar(100))
begin
  declare
    @xml  xml;
  select
    @sep  = isnull(@sep,';'),
    @list = replace(@list,@sep,nchar(3)),
    @list = replace(@list,'&','&amp;'),
    @list = replace(@list,'"','&quot;'),
    @xml  = '<r c="' + replace(@list,nchar(3),'"/><r c="') + '"/>';

  with tbl as
   (select name = ltrim(ltrim(c.value('@c','nvarchar(100)'))) from @xml.nodes('/r') t(c))
  insert @tbl(name) select
    name
  from
    tbl
  where
    name <> '';

  return;
end;
go
9 окт 17, 17:58    [20855499]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
Ролг Хупин
расклёпаная строка
Картинка есть? :)
9 окт 17, 18:13    [20855559]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
aleks222
Guest
invm
aleks222
Редкостный бред.

То же самое, только поэффективней.
Когда за дело берется всеми нами любимый гуру оптимизации и эффективности, обычно получается вот так:
+ 
use tempdb;
go

create FUNCTION [dbo].[Редкостный бред]
(
   @List NVARCHAR(4000),
   @Delimiter NVARCHAR(5)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL 
						SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
  FROM cteStart s;
go

create function [dbo].[То же самое, только поэффективней](@str nvarchar(4000), @delimiter nvarchar(5)=',')
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
)
go

create table dbo.t (id int identity primary key, s nvarchar(4000));
insert into dbo.t
select top (10000)
 substring(c.x.value('.', 'nvarchar(4000)'), 2, 4000)
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b cross apply
 (
  select top (cast(rand(checksum(newid()) + a.number) * 500 as int) + 1)
   ',' + name
  from
   master.dbo.spt_values
  for xml path(''), type
 ) c(x);
go

declare @c int;

set statistics time on;

select
 @c = count(*)
from
 dbo.t cross apply
 dbo.[Редкостный бред](t.s, ',') a
option
 (maxdop 1)

select
 @c = count(*)
from
 dbo.t cross apply
 dbo.[То же самое, только поэффективней](t.s, ',') a
option
 (maxdop 1, maxrecursion 0)

set statistics time off;
go

drop table dbo.t;
drop function dbo.[То же самое, только поэффективней], dbo.[Редкостный бред];
go

Редкостный бред43 ms.
То же самое, только поэффективней28257 ms.


RegisteredUser
iap
Да! На форуме есть и другие аналогичные функции, и даже более универсальные (для набора символов-разделителей, например).


Опять чуть более, чем бессмысленный тест?


Вот это сейчас как раз актуальненко.
Хотелось бы как-то расклепать строку вида

'dd-ff, ff-gg, yy-kk....'
-- где  ',' - разделитель для строк
--  а '-' - разделитель для полей


Cross apply разучи, страдалец.
9 окт 17, 18:44    [20855649]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
RegisteredUser
iap
Да! На форуме есть и другие аналогичные функции, и даже более универсальные (для набора символов-разделителей, например).



Вот это сейчас как раз актуальненко.
Хотелось бы как-то расклепать строку вида

'dd-ff, ff-gg, yy-kk....'
-- где  ',' - разделитель для строк
--  а '-' - разделитель для полей
Ну вот, напримерп, тема:
Функция, которая делит строку на слова
Кстати, там и один из вариантов Алекса есть
Ну, и мой, естественно
9 окт 17, 19:26    [20855741]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
RegisteredUser
Опять чуть более, чем бессмысленный тест?
Шо, опять!? (с)

Может уже озвучишь критерии осмысленности?
А еще лучше продемонстрируй уже какой-нибудь "осмысленный тест", показывающий преимущества твоих эффективных решений.
9 окт 17, 20:39    [20855934]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
RegisteredUser
Member

Откуда:
Сообщений: 585
invm
Ролг Хупин
расклёпаная строка
Картинка есть? :)


'dd-ff-aa, ff-gg-ss, yy-kk-gg....'
-- где  ',' - разделитель для строк
--  а '-' - разделитель для полей


хАчЮ так:


Field1    |   Field2    |   Field3   |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   dd     |   ff        |    aa      |
   ff     |   gg        |    ss      |
   yy     |   kk        |    gg      |
10 окт 17, 09:28    [20856561]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
 		STRING_SPLIT
10 окт 17, 10:39    [20856845]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
RegisteredUser
хАчЮ так:


Field1    |   Field2    |   Field3   |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   dd     |   ff        |    aa      |
   ff     |   gg        |    ss      |
   yy     |   kk        |    gg      |
declare @s varchar(max) = 'dd-ff-aa, ff-gg-ss, yy-kk-gg';

select
 t.n.value('b[1]', 'varchar(10)'),
 t.n.value('b[2]', 'varchar(10)'),
 t.n.value('b[3]', 'varchar(10)')
from
 (select cast('' as xml).query('sql:variable("@s")').value('.', 'varchar(max)')) a(s) cross apply
 (select cast('<a><b>' + replace(replace(a.s, '-', '</b><b>'), ',', '</b></a><a><b>') + '</b></a>' as xml)) b(x) cross apply
 b.x.nodes('a') t(n);

Функцию из этого сделаете сами.
10 окт 17, 11:28    [20856999]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
 		Исправленный вариант
declare @s varchar(max) = 'dd-ff-aa&, ff-gg-ss, yy-kk-gg';

select
 t.n.value('b[1]', 'varchar(10)'),
 t.n.value('b[2]', 'varchar(10)'),
 t.n.value('b[3]', 'varchar(10)')
from
 (select cast(cast('' as xml).query('sql:variable("@s")') as varchar(max))) a(s) cross apply
 (select cast('<a><b>' + replace(replace(a.s, '-', '</b><b>'), ',', '</b></a><a><b>') + '</b></a>' as xml)) b(x) cross apply
 b.x.nodes('a') t(n);
10 окт 17, 11:32    [20857014]     Ответить | Цитировать Сообщить модератору
 Re: Как это работает?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3653
iap
STRING_SPLIT


автор
Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) Jul 8 2017 16:43:40 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
10 окт 17, 13:04    [20857318]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить