1

Topic: How it works?

Colleagues!
The monster the programmer of thought (the code see more low) got to us here such here.
Function well works - remarks are not present (result of its operation on a screen)
Question such:
Explain as it works?)))))

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;

2

Re: How it works?

RegisteredUser;
Character-by-character smile

3

Re: How it works?

a line with the table of numbers
cteTally (N) from 0 to <length of a line>;
(I.e. tore a line vertically;
Near each number the appropriate character of a line;
And  only on a separator);
Received separator positions cteStart.
Well everything, now we remove substrings from the found position of a separator
To a following separator

4

Re: How it works?

Elementarily.
The first 4 CTE - only for generation of the table with a considerable quantity of lines.
cteStart (N1) receives separator positions in line and allocates these numbers in the generated table in the form of lines.
The main request takes these positions and searches for the first separator after cteStart (N1), produces substring between these positions.
However, ISNULL (@List, 1) it is simple bosh dog.
Yes! At a forum there are also other similar functions, and even more general-purpose (for a dial-up of delimiting characters, for example).
And the decision of this task by means of XML, and it is a lot of that else...
It is necessary to look

5

Re: How it works?

RegisteredUser wrote:

Colleagues!
The monster the programmer of thought (the code see more low) got to us here such here.
Function well works - remarks are not present (result of its operation on a screen)
Question such:
Explain as it works?)))))

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;

Rare delirium.
The same, only is more effective.

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
)

6

Re: How it works?

-, and even gaps did not cut off
:-D

7

Re: How it works?

aleks222 wrote:

Rare delirium.
The same, only is more effective.

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
)

On similarity of this I already had a function.
Me amazed that with CTE

8

Re: How it works?

iap wrote:

Yes! At a forum there are also other similar functions, and even more general-purpose (for a dial-up of delimiting characters, for example).

Here it now just .
It would be desirable somehow  a line of a type

' dd-ff, ff-gg, yy-kk....'
-- Where ', ' - a separator for lines
-- And ' - ' - a separator for fields

9

Re: How it works?

RegisteredUser wrote:

it is passed...
Here it now just .
It would be desirable somehow  a line of a type

' dd-ff, ff-gg, yy-kk....'
-- Where ', ' - a separator for lines
-- And ' - ' - a separator for fields

@@version?

10

Re: How it works?

Hupin wrote:

it is passed...
@@version?

Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) Jul 8 2017 4:43:40 PM Copyright (c) Standard Edition Microsoft Corporation (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

11

Re: How it works?

aleks222 wrote:

Rare delirium.
The same, only is more effective.

When all of us the favourite guru of optimization and efficiency for good reason undertakes, normally it turns out here so: [spoiler]

use tempdb;
go
create FUNCTION [dbo]. [Rare delirium]
(
@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]. [The Same, only is more effective] (@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. [Rare delirium] (t.s, ', ') a
option
(maxdop 1)
select
@c = count (*)
from
dbo.t cross apply
dbo. [The same, only is more effective] (t.s, ', ') a
option
(maxdop 1, maxrecursion 0)
set statistics time off;
go
drop table dbo.t;
drop function dbo. [The Same, only is more effective], dbo. [Rare delirium];
go

[CSV =;];
Rare delirium; 43 ms.
The same, only is more effective; 28257 ms.
[/spoiler]

12

Re: How it works?

RegisteredUser wrote:

It would be desirable somehow  a line of a type

And what should as a result it turns out?

13

Re: How it works?

invm wrote:

it is passed...
Also what should as a result it turns out?

a line
:-D

14

Re: How it works?

Variant to a heap:

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, ' AND ', ' & ');
@list = replace (@list, ' "', ' " ');
@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

15

Re: How it works?

Hupin wrote:

  a line
:-D

the Picture is? smile

16

Re: How it works?

invm wrote:

it is passed...
When all of us the favourite guru of optimization and efficiency for good reason undertakes, normally it turns out here so: [spoiler]

use tempdb;
go
create FUNCTION [dbo]. [Rare delirium]
(
@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]. [The Same, only is more effective] (@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. [Rare delirium] (t.s, ', ') a
option
(maxdop 1)
select
@c = count (*)
from
dbo.t cross apply
dbo. [The same, only is more effective] (t.s, ', ') a
option
(maxdop 1, maxrecursion 0)
set statistics time off;
go
drop table dbo.t;
drop function dbo. [The Same, only is more effective], dbo. [Rare delirium];
go

[CSV =;];
Rare delirium; 43 ms.
The same, only is more effective; 28257 ms.
[/spoiler]

RegisteredUser wrote:

it is passed...
Again hardly more, than the senseless test?
Here it now just .
It would be desirable somehow  a line of a type

' dd-ff, ff-gg, yy-kk....'
-- Where ', ' - a separator for lines
-- And ' - ' - a separator for fields

Cross apply study, the sufferer.

17

Re: How it works?

RegisteredUser wrote:

it is passed...
Here it now just .
It would be desirable somehow  a line of a type

' dd-ff, ff-gg, yy-kk....'
-- Where ', ' - a separator for lines
-- And ' - ' - a separator for fields

Well here, , a subject:
Function which divides a line into words
By the way, there and one of Alex's variants is:-D
Well, and mine, naturally

18

Re: How it works?

RegisteredUser wrote:

Again hardly more, than the senseless test?

Sho, again!? (:-D
Can already you will sound criteria of conciseness?
And is even better show already any "the intelligent test", showing advantages of your effective decisions.

19

Re: How it works?

invm wrote:

it is passed...
The picture is? smile

' dd-ff-aa, ff-gg-ss, yy-kk-gg....'
-- Where ', ' - a separator for lines
-- And ' - ' - a separator for fields

so:

Field1 | Field2 | Field3 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
dd | ff | aa |
ff | gg | ss |
yy | kk | gg |

20

Re: How it works?

STRING_SPLIT

21

Re: How it works?

RegisteredUser wrote:

  so:

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);

Function from this make.

22

Re: How it works?

Corrected variant

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);

23

Re: How it works?

iap wrote:

STRING_SPLIT

wrote:

Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) Jul 8 2017 4:43:40 PM Copyright (c) Standard Edition Microsoft Corporation (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)