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

Откуда: Киев
Сообщений: 434
Имеется переменная
declare @s varchar(max) = 
'abc123
def3
ghi4
jkl7657
mno2'

Можно ли эту переменную красиво ) загрузить в таблицу #tmp состоящую из одного столбца Name типа varchar(20) - каждую строчку в отдельную строку таблицы? Или нужно всё это парсить "вручную"?
2 окт 12, 12:24    [13254747]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
https://www.sql.ru/articles/mssql/03060701arraysandlistsinsqlserver.shtml
2 окт 12, 12:25    [13254760]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Функция, которая делит строку на слова

А вообще, тут тем про это - тыщи!

Если Вы это делаете для передачи списка в функцию или в процедуру,
а у Вас 2008-й сервер (или новее), то лучше заполнять табличную переменную
значениями (вместо их склеивания в строку через разделитель) и передавать через табличный параметр.
Итого - не надо ни формирования списка, ни парсинга. Правда, требуется заполнение табличной переменной.
2 окт 12, 12:41    [13254862]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Leax
Member

Откуда: Киев
Сообщений: 434
Спасибо. Я сам уже написал требуемый парсинг строки в несколько строк таблицы. Просто думал, что может быть более элегантное решение...
2 окт 12, 12:49    [13254937]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Leax
Спасибо. Я сам уже написал требуемый парсинг строки в несколько строк таблицы. Просто думал, что может быть более элегантное решение...
И что, нету?
Хотелось бы посмотреть
2 окт 12, 13:22    [13255254]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Leax
Member

Откуда: Киев
Сообщений: 434
iap
Leax
Спасибо. Я сам уже написал требуемый парсинг строки в несколько строк таблицы. Просто думал, что может быть более элегантное решение...
И что, нету?
Хотелось бы посмотреть

Решение (более элегантное =) нашел по ссылке, но там тоже парсинг "в лоб" получается...
2 окт 12, 13:39    [13255379]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
Leax
Просто думал, что может быть более элегантное решение...
каков критерий элегантности?
2 окт 12, 14:02    [13255566]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Leax
Member

Откуда: Киев
Сообщений: 434
Дедушка,
сделать это одним-двумя Select'ами
2 окт 12, 15:08    [13256219]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Там же
DECLARE	@String	VarChar(max) = 
'abc123
def3
ghi4
jkl7657
mno2'
--------------------
SELECT	N.X.value('text()[1]','VarChar(max)')
FROM	(SELECT Convert(XML,'<s>' + replace(@String,Char(13),'</s><s>') + '</s>'))S(X)
	CROSS APPLY S.X.nodes('/s')N(X)
Только работать не будет если будет всякие левые символы в таком тексте.
2 окт 12, 15:34    [13256507]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Вот так будет:
DECLARE	@String	VarChar(max) = 
'abc123"
def3&quote;
ghi4<A>
jkl7657</A>
mno2'
--------------------
SELECT	N.X.value('text()[1]','VarChar(max)')
FROM	(SELECT Convert(XML,'<s>' + Replace((SELECT @String FOR XML Path('')),'&#x0D;','</s><s>') + '</s>'))S(X)
	CROSS APPLY S.X.nodes('/s')N(X)
2 окт 12, 15:38    [13256543]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104760
declare @s varchar(max) 
set @s = '"abc123
def3
ghi4
jkl7657
mno2"'
SELECT display_term, source_term FROM sys.dm_fts_parser (@s, 1033, 0, 0)
2 окт 12, 15:55    [13256713]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
declare @s varchar(max) 
set @s = 'abc123
def3~!@#$%^&*()_+`-=;:"<>?,./\|
ghi4
jkl7657
mno2'
SELECT	display_term
FROM	sys.dm_fts_parser(QuoteName(@s,'"'),1033,0,0)
Doh
Жаль что съедает символы (а ` это слово).
2 окт 12, 16:34    [13257080]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
эт только если fts установлен...
2 окт 12, 16:45    [13257205]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Мой вариант

declare @str varchar(max), @delimiter varchar(max);
set @delimiter=char(13)+char(10); 
set @str = 'abc123
def3
ghi4
jkl7657
mno2
drtyeryuery
eryertyertyuyu
gfhdfg';
with pz0 as (
select charindex(@delimiter,@str) a
union all select charindex(@delimiter,@str,a+datalength(@delimiter)) from pz0 where a>0
),
pz as (
select 1 as a, min(a)-1 as b  from pz0 where a>0
union all select a+datalength(@delimiter), IsNull((select min(p1.a) from pz0 p1 where p1.a>p.a)-1,len(@str)) from pz0 p where a>0
) 
select substring(@str, a, b-a+1) as line from pz order by a;


Сообщение было отредактировано: 2 окт 12, 17:09
2 окт 12, 16:46    [13257220]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
ЕвгенийВ
Member

Откуда: Москва
Сообщений: 4951
CREATE FUNCTION [dbo].[SplitString] (@sep nchar(1), @s nvarchar(512)) 
RETURNS table 
AS RETURN 
(     
	WITH Pieces(pn, start, [stop]) 
AS 
(       
	SELECT 1, 1, CHARINDEX(@sep, @s)       
	UNION ALL       
	SELECT pn + 1, [stop] + 1, CHARINDEX(@sep, @s, [stop] + 1)       
	FROM Pieces       
	WHERE [stop] > 0     
)     
	
	SELECT pn, SUBSTRING(@s, start, 
	CASE 
		WHEN [stop] > 0 THEN [stop]-start 
		ELSE 512 
	END
	) AS s     
FROM Pieces   
)
2 окт 12, 17:51    [13257674]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Лучше бы я не выкладывал XML вариант, а то сразу топик в ЖЖ превращается.
2 окт 12, 18:16    [13257824]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Валдай
Member

Откуда:
Сообщений: 113
Mnior
Лучше бы я не выкладывал XML вариант, а то сразу топик в ЖЖ превращается.

Не учи плохому:) варинт парсинга с xml самый короткий, но и самый тормозной.
2 окт 12, 18:21    [13257862]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Валдай
варинт парсинга с xml самый короткий, но и самый тормозной.
А вы уверены или вывели гипотетически? :)
Многие тоже так считали. Вот возьми и испытай все варианты. И во сколько раз.

А второе, это был ответ на 13255379. Который был "соффременной" адаптацией из первого же ответа (Сергея Алексеевича).
Не увидев этого варианта в указанных линках, решил заодно и показать. А далее поехал копипаст ... (13254862)
2 окт 12, 22:20    [13258620]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Валдай
варинт парсинга с xml самый короткий, но и самый тормозной.

ВариантВремя
13256507 Xml1183 (не надёжный)
13256543 Xml2597
13257080 FTS (Glory)140 (неправильный результат)
13257220 CTE1 (Глеб)603
13257674 CTE2 (ЕвгенийВ)143
Так что работу с XML очень даже неплохо реализовали.
Вот в одно время iljy показывал, что не стоит отбрасывать.
А CTE не бесплатный.
3 окт 12, 11:03    [13260133]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Mnior
А CTE не бесплатный.
Постоянно требует денежный перевод в Редмонд?
3 окт 12, 11:29    [13260384]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Валдай
Member

Откуда:
Сообщений: 113
Mnior,

Ок :)

вот вариантик набросал

IF OBJECT_ID (N'dbo.SplitString_CTE') IS NOT NULL
    DROP FUNCTION dbo.SplitString_CTE
GO

CREATE FUNCTION dbo.SplitString_CTE(@List Varchar(MAX), @Delimiter VarChar(1)=',')
RETURNS TABLE
AS RETURN
(
  WITH 
    CTE AS (
      SELECT 
        RowValue = SUBSTRING( List, 1, p - 1 ),
        List     = STUFF( List, 1, p, '' )
      FROM ( SELECT List = @List + @Delimiter )S 
      CROSS APPLY ( SELECT p = CHARINDEX( @Delimiter, List ) )p
      WHERE p > 0
      UNION ALL 
      SELECT 
        RowValue = SUBSTRING( List, 1, p - 1 ),
        List     = STUFF( List, 1, p, '' )
      FROM CTE 
      CROSS APPLY ( SELECT p = CHARINDEX( @Delimiter, List ) )p
      WHERE p > 0
    )
   SELECT RowValue FROM CTE
)
GO


возмем кусок подлиннее.

SET STATISTICS TIME ON
GO
DECLARE @List VarChar(MAX)
SET @List = REPLICATE(cast('abc123
def3~!@#$%^&*()_+`-=;:"<>?,./\|
ghi4
jkl7657
mno2
' as varchar(max)), 1000)
PRINT'--CTE--'
SELECT RowValue FROM dbo.SplitString_CTE(@List,  char(10))
OPTION (maxrecursion 0)

PRINT'--XML--'
SELECT RowValue =N.X.value('text()[1]','VarChar(max)')
FROM	(SELECT Convert(XML,'<s>' + Replace((SELECT @List FOR XML Path('')),'
','</s><s>') + '</s>'))S(X)
	CROSS APPLY S.X.nodes('/s')N(X)


(2005-й)

time
SQL Server Execution Times:
--CTE--
CPU time = 890 ms, elapsed time = 902 ms.
--XML--
CPU time = 365813 ms, elapsed time = 366894 ms.
3 окт 12, 11:30    [13260389]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Leax
Member

Откуда: Киев
Сообщений: 434
Обалдеть, сколько вариантов! Спасибо! :)
3 окт 12, 12:36    [13260955]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Валдай,
во первых ваш варианты не работают должным образом.
А во вторых CPU time и Client Time разные. То что XML есть больше проца - это да, только вот я измерял время работы.
А в третьих, если вы пошли по ссылке и посмотрели то видно что для разных входных данных результат разный.
То что вы сделали для экстремально больших значений, то это конечно только ради забавы, ибо в таком случае это издевательство над сервером.

Если запустить явно ваш пример, то получается:
--CTE--
   Время ЦП = 1279 мс, затраченное время = 1629 мс.
--XML--
 Время работы SQL Server:
   Время ЦП = 78 мс, затраченное время = 69 мс.

Так что будъте внимательнее. В всё потому что вы ошиблись:
Валдай
PRINT'--XML--'
SELECT RowValue =N.X.value('text()[1]','VarChar(max)')
FROM	(SELECT Convert(XML,'<s>' + Replace((SELECT @List FOR XML Path('')),'
','</s><s>') + '</s>'))S(X)
	CROSS APPLY S.X.nodes('/s')N(X)
-- Надо
PRINT'--XML--'
SELECT RowValue =N.X.value('text()[1]','VarChar(max)')
FROM	(SELECT Convert(XML,'<s>' + Replace((SELECT @List FOR XML Path('')),'&#x0D;','</s><s>') + '</s>'))S(X)
	CROSS APPLY S.X.nodes('/s')N(X)
В четвёртых, возврат все этих 5 тыс срок на клиент это затрагивать с ним взаимодействие. Это надо убирать в тестах:
SELECT @Var = RowValue

Клиентское время для XML1 и вашего CTE для больших строк:
Trial 2 (XML)Trial 1 (CTE)
Total execution (500)201578
Total execution (5000)2057491249
Упёрся в оперативу.

Ну и на последок, вы также как и Глеб сделали ошибки в вашем CTE, слишком много телодвижений. Посмотрите внимательнее вариант ЕвгенийВ.
ЕвгенийВ
CHARINDEX(@sep, @s, [stop] + 1)
Не нужно генерировать промежуточные строки и забивать оперативку. Поэтому и такая разница (CTE1 и CTE2).

И в шестых, как уже говорил мой ответ был на это:
Leax
Спасибо. Я сам уже написал требуемый парсинг строки в несколько строк таблицы. Просто думал, что может быть более элегантное решение...
Я никак не претендовал на скорость. Никак.
Так что ваш коммент просто не по теме.
3 окт 12, 17:05    [13263163]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Валдай
Member

Откуда:
Сообщений: 113
Mnior,
Mnior
Так что будъте внимательнее. В всё потому что вы ошиблись:

Да плохо откопипастил, код преобразовался в реальный пренос строки.
Mnior
во первых ваш варианты не работают должным образом.

Хм. А что не так?
Mnior
То что вы сделали для экстремально больших значений, то это конечно только ради забавы

5000 значений через разделитель это много?? в случае строк м.б. а в случае просто чисел ничего особенного.

SELECT @Var = RowValue

а вот это почему?
почему не insert, например, в табличную переменную?

Mnior
Не нужно генерировать промежуточные строки и забивать оперативку

Да, взял на заметку вариант от ЕвгенийВ

Mnior
Так что ваш коммент просто не по теме.

странно что вы мне это не написали несколько постов назад:)
3 окт 12, 18:10    [13263492]     Ответить | Цитировать Сообщить модератору
 Re: парсинг строк в таблицу  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Валдай
Mnior
во первых ваш варианты не работают должным образом.
Хм. А что не так?
Mnior
В всё потому что вы ошиблись:
Да плохо откопипастил.
Вот и вопрос отпал.
Валдай
5000 значений через разделитель это много??
Очень, тут уже нужно табличные параметры юзать. Точнее их надо юзать в любом, но так 100 пудова.
Валдай
в случае строк м.б. а в случае просто чисел ничего особенного.
Абсолютно без разницы. Строки это моветон. Чисто временные костыли.
Валдай
SELECT @Var = RowValue
а вот это почему?
почему не insert, например, в табличную переменную?
Потому что перелинковка переменной на новое значение, это не создание структуры в tempdb и заливка данных. Оно займёт дофига времени, скорее больше чем парсинг (это неважно, главное что сопоставимо).
В итоге мы тестируем не то что нужно.
Более того, присвоение будет только для последней строки, остальные вообще skipятся (не уверен, но должно).
Валдай
Mnior
Так что ваш коммент просто не по теме.
странно что вы мне это не написали несколько постов назад ;)
Ок. Согласен что это очевидно, но просто для контроля..

Мода пошла: троллить - это круто. А если ты не троллишь то ты динозавр.
Смысл? Кому он нафиг сдался.
Ах нуда, мы все такие вумные что можем догадаться до всего. Ага, прям мысли читаем.
.
3 окт 12, 21:50    [13264352]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить