1

Topic: Help to write request

Good evening.
As from a line
\ext_bulk_insert \[2] \product_layout\loop \[1] \delete ext
To receive two lines:
\ext_bulk_insert \[2]
\ext_bulk_insert \[2] \product_layout\loop \[1]
Implemented as follows:

;with dat as
(
select
value;
ROW_NUMBER () over (order by 1/0) as rn
from
string_split ('\ext_bulk_insert \[2] \product_layout\loop \[1] \delete ext ', '] ')
where
value like ' % [[] %'
)
, cte as
(
select
cast (value + '] ' as nvarchar (255)) as value;
rn
from
dat
where
rn = 1
union all
select
cast (cte.value + '] ' + dat.value + '] ' as nvarchar (255)) as value;
cte.rn + 1 as rn
from
cte
inner join dat on
cte.rn + 1 = dat.rn
)
select * from cte

Whether but it is possible as that to implement easier? (Usage of the regular expressions on the basis of clr-functions) can try

2

Re: Help to write request

DECLARE @S VARCHAR (100) = '\ext_bulk_insert \[2] \product_layout\loop \[1] \delete ext ';
SELECT LEFT (@S, CHARINDEX ('] ' ,@S)), LEFT (@S, CHARINDEX ('] ' ,@S,CHARINDEX ('] ' ,@S) +1));

3

Re: Help to write request

iap wrote:

DECLARE @S VARCHAR (100) = '\ext_bulk_insert \[2] \product_layout\loop \[1] \delete ext ';
SELECT LEFT (@S, CHARINDEX ('] ' ,@S)), LEFT (@S, CHARINDEX ('] ' ,@S,CHARINDEX ('] ' ,@S) +1));

Hello, jap. I see that not in full made the task description: number of square brackets always the indefinite.
That is if the line is a following type: DECLARE @S VARCHAR (100) = '\ext_bulk_insert \[2] \product \[5] \product_layout\loop \[1] \delete ext ';
In that case the result should be the following:
\ext_bulk_insert \[2]
\ext_bulk_insert \[2] \product \[5]
\ext_bulk_insert \[2] \product \[5] \product_layout\loop \[1]

4

Re: Help to write request

rsolanov;
For a SQL Server 2016

declare @s varchar (100) = '\ext_bulk_insert \[2] \product \[5] \product_layout\loop \[1] \delete ext ';
declare @pieces table (i int identity primary key, p varchar (100), is_bracketed_number bit);
insert into @pieces
(p, is_bracketed_number)
select
value;
case when value not like ' [% [^0123456789] %] ' then 0 else 1 end
from
string_split (@s, ' \')
where
value> ";
select
b.x.value ('. ', ' varchar (100) ')
from
(select i from @pieces where is_bracketed_number = 1) a cross apply
(select ' \' + p from @pieces where i <= a.i order by i for xml path ("), type) b (x);

For other versions search at a forum or google any function of a partition sinks on a separator.

5

Re: Help to write request

rsolanov wrote:

Hello, jap. I see that not in full made the task description: number of square brackets always the indefinite.

Greetings!
But the first letter not j, and i
If to make recursive CTE, searching entrance in a line of a right square bracket;
On each following step it is possible to search for that function CHARINDEX with the third parameter;
Equal to a position found on the previous step, plus 1.

6

Re: Help to write request

iap, really the request turned out more optimal:

declare @s varchar (100) = '\ext_bulk_insert \[2] \product \[5] \product_layout\loop \[1] \delete ext ';
;with
cte as
(
select
left (@s, dat.i) as value;
dat.i as i
from
(select CHARINDEX ('] ', @s) as i) as dat
union all
select
left (@s, dat.i) as value;
dat.i
from
(select CHARINDEX ('] ', @s, cte.i + 1) as i from cte) as dat
where
dat.i> 0
)
select * from cte

7

Re: Help to write request

rsolanov;
It is possible to manage and without a recursion if to involve the table with enough considerable quantity of lines.
Even if in it there will be no field with the continuous row of integer numbers, lines can be enumerated ROW_NUMBER () an ohm
And for every line to take n- the character of an initial line, then numbers of numbering for lines with right
a bracket give the second function parameter LEFT ().
All it to issue subqueries or equivalent not recursive CTE.

8

Re: Help to write request

iap wrote:

rsolanov;
It is possible to manage and without a recursion if to involve the table with enough considerable quantity of lines.
Even if in it there will be no field with the continuous row of integer numbers, lines can be enumerated ROW_NUMBER () an ohm
And for every line to take n- the character of an initial line, then numbers of numbering for lines with right
a bracket give the second function parameter LEFT ().
All it to issue subqueries or equivalent not recursive CTE.

did not understand your thought, it is primary after all a line one, explain please if algorithm not difficult offered by you more in detail

9

Re: Help to write request

rsolanov;
Somehow here so

DECLARE @s VARCHAR (200) = '\ext_bulk_insert \[2] \product \[5] \product_layout\loop \[1] \delete ext'
;
WITH
[nn] AS (
SELECT
[rn]
FROM
(SELECT [rn] = ROW_NUMBER () OVER (ORDER BY 1/0) FROM master. spt_values) t
WHERE
[rn] <= LEN (@s)
)
SELECT
[value] = LEFT (@s, [rn]);
[i] = [rn]
FROM
[nn]
WHERE
SUBSTRING (@s, [rn], 1) = ']'
;

10

Re: Help to write request

rsolanov;
Here to you for speculations on a subject of parsing of lines the methods offered above: [spoiler the Test]

use tempdb;
go
create function dbo.fnSplitString
(
@s nvarchar (max);
@delimeter nvarchar (100) = N ','
)
returns table
as
return (
with s as
(
select
c.d, d.s
from
(select N ' [' + @delimeter + N '] ') a (d) cross apply
(select replace (@s, @delimeter, a.d)) b (s) cross apply
(select cast (cast ("as xml).query (' sql:column ("a.d") ') as varchar (max))) c (d) cross apply
(select cast (cast ("as xml).query (' sql:column ("b.s") ') as varchar (max))) d (s)
)
select
row_number () over (order by (select 1)) as ValueOrder;
b.n.value ('. ', ' nvarchar (max) ') as Value
from
(select cast (N ' <item> ' + replace (s.s, s.d, N ' </item> <item> ') + N ' </item> ' as xml) from s) a (x) cross apply
a.x.nodes ('/item ') b (n)
);
go
create function dbo.fn1
(
@s VARCHAR (200)
)
returns table
as
return (
WITH
[nn] AS (
SELECT
[rn]
FROM
(SELECT [rn] = ROW_NUMBER () OVER (ORDER BY 1/0) FROM master. spt_values) t
WHERE
[rn] <= LEN (@s)
)
SELECT
[value] = LEFT (@s, [rn])
FROM
[nn]
WHERE
SUBSTRING (@s, [rn], 1) = ']'
);
go
create function dbo.fn2
(
@s VARCHAR (200)
)
returns table
as
return (
with
cte as
(
select
left (@s, dat.i) as value;
dat.i as i
from
(select CHARINDEX ('] ', @s) as i) as dat
union all
select
left (@s, dat.i) as value;
dat.i
from
(select CHARINDEX ('] ', @s, cte.i + 1) as i from cte) as dat
where
dat.i> 0
)
select value from cte
)
go
create function dbo.fn3
(
@s VARCHAR (200)
)
returns table
as
return (
with t (i, p, is_bracketed_number) as
(
select
ValueOrder;
value;
case when value like '! [%!] ' escape '! ' then 1 else 0 end
from
dbo.fnSplitString (@s, ' \')
where
value>"
)
select
b.x.value ('. ', ' varchar (100) ') as value
from
(select i from t where is_bracketed_number = 1) a cross apply
(select ' \' + p from t where i <= a.i order by i for xml path ("), type) b (x)
);
go
declare @s varchar (200) = '\ext_bulk_insert \[2] \product \[5] \product_layout\loop \[1] \delete ext ';
create table dbo.t (id int identity primary key, s varchar (200));
insert into dbo.t
select top (1000000)
@s
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;
go
declare @c int;
set statistics time on;
select
@c = count (*)
from
dbo.t cross apply
dbo.fn1 (s) f
option
(maxdop 1);
select
@c = count (*)
from
dbo.t cross apply
dbo.fn2 (s) f
option
(maxdop 1);
select
@c = count (*)
from
dbo.t cross apply
dbo.fn3 (s) f
option
(maxdop 1);
set statistics time off;
go
drop table dbo.t;
drop function dbo.fn1, dbo.fn2, dbo.fn3, dbo.fnSplitString;
go

[/spoiler] [spoiler Result]

 (1000000 rows affected)
SQL Server operating time:
CPU time = 22917 msec, expended time = 23102 msec.
SQL Server operating time:
CPU time = 47689 msec, expended time = 48765 msec.
SQL Server operating time:
CPU time = 1248 msec, expended time = 1249 msec.

[/spoiler]

11

Re: Help to write request

invm, big to you thanks for such research. Here did not think that dbo.fn3 appears the fastest method on great volumes of the data.