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

Откуда:
Сообщений: 930
Добрый вечер.
как из строки
\ext_bulk_insert\[2]\product_layout\loop\[1]\delete ext
получить две строки:
\ext_bulk_insert\[2]
\ext_bulk_insert\[2]\product_layout\loop\[1]

Реализовал следующим образом:
;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

Но можно ли как то реализовать проще? (может попробовать использование регулярных выражений на основе clr-функций)
1 дек 17, 17:37    [20999201]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
скт
Guest
rsolanov,

Размножить таблицу на 2, и через charindex разбить строки?
1 дек 17, 18:13    [20999303]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
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));
1 дек 17, 18:22    [20999324]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
iap
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));
Здравствуйте, jap. Вижу что не в полном объеме сделал описание задачи: число квадратных скобок всегда неопределенное.
То есть если строка будет следующего вида: DECLARE @S VARCHAR(100)='\ext_bulk_insert\[2]\product\[5]\product_layout\loop\[1]\delete ext';
В таком случае результат должен быть следующим:
\ext_bulk_insert\[2]
\ext_bulk_insert\[2]\product\[5]
\ext_bulk_insert\[2]\product\[5]\product_layout\loop\[1]
3 дек 17, 12:56    [21002013]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
rsolanov,

Для 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);

Для других версий ищите на форуме или гуглите любую функцию разбиения стоки по разделителю.
3 дек 17, 14:15    [21002128]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
rsolanov
Здравствуйте, jap. Вижу что не в полном объеме сделал описание задачи: число квадратных скобок всегда неопределенное.
Привет!
Но первая буква не j, а i

Если сделать рекурсивное CTE, ищущее вхождение в строку правой квадратной скобки,
то на каждом следующем шаге можно искать функцией CHARINDEX с третьим параметром,
равным позиции, найденной на предыдущем шаге, плюс 1.
3 дек 17, 16:59    [21002371]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
iap, действительно запрос получился более оптимальный:
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
4 дек 17, 12:48    [21004019]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
rsolanov,

можно обойтись и без рекурсии, если задействовать таблицу с достаточно большим количеством строк.
Даже если в ней не будет поля с непрерывным рядом целых чисел, строки можно пронумеровать ROW_NUMBER()ом
и для каждой строки взять n-й символ исходной строки, после чего числа нумерации для строк с правой
квадртной скобкой дадут второй параметр функции LEFT().
Всё это оформить подзапросами или эквивалентными нерекурсивными CTE.
4 дек 17, 13:05    [21004091]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

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

можно обойтись и без рекурсии, если задействовать таблицу с достаточно большим количеством строк.
Даже если в ней не будет поля с непрерывным рядом целых чисел, строки можно пронумеровать ROW_NUMBER()ом
и для каждой строки взять n-й символ исходной строки, после чего числа нумерации для строк с правой
квадртной скобкой дадут второй параметр функции LEFT().
Всё это оформить подзапросами или эквивалентными нерекурсивными CTE.
Не понял вашу мысль, изначально ведь строка одна, поясните пожалуйста если не сложно предложенный вами алгоритм подробнее
4 дек 17, 14:41    [21004469]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
rsolanov,
Как-нибудь вот так
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 ) = ']'
;
4 дек 17, 14:58    [21004532]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
rsolanov,

Вот вам для размышлений на тему парсинга строк предложенными выше способами:
+ Тест
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
+ Результат
(1000000 rows affected)

Время работы SQL Server:
Время ЦП = 22917 мс, затраченное время = 23102 мс.

Время работы SQL Server:
Время ЦП = 47689 мс, затраченное время = 48765 мс.

Время работы SQL Server:
Время ЦП = 1248 мс, затраченное время = 1249 мс.
4 дек 17, 15:49    [21004763]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
invm, большое вам спасибо за такое исследование. Вот уж не думал что dbo.fn3 окажется самым быстрым способом на больших объемах данных.
5 дек 17, 16:35    [21008090]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить