#sql #sql-server #string
#sql #sql-server #строка
Вопрос:
Я нашел эту функцию разделения строки SQL в Интернете, но когда я передаю строку, содержащую более 700 элементов, разделенных запятыми, она генерирует только табличную переменную с 280 строками, может кто-нибудь помочь мне определить, в чем проблема?
Код:
ALTER FUNCTION [dbo].[fn_Split](@text nvarchar(MAX), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value nvarchar(MAX)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
Это код, который я использовал для ее тестирования:
SELECT * FROM fn_Split(@string,',');
@String — это nvarchar (МАКСИМУМ), содержащий более 700 элементов, но он возвращает только таблицу с 280 строками.
Комментарии:
1. Код, который вы используете для тестирования, ссылается на вызываемую функцию
LRMWEB_fn_Split
, однако приведенный выше код определяет вызываемую функциюfn_Split
. Вы уверены, что вызываете правильную функцию? Кроме того, какой длины ваш @string-параметр? Обратите внимание, что nvarchar (MAX) не может содержать более 4000 символов.2. @Dan, извините, это просто опечатка при написании вопроса, исправлено.
3. Хорошо — как насчет длины содержимого, входящего в параметр @string? Она будет усечена на 4000 символов, что означает, что вы получите только ~ 280 элементов, если их средняя длина равна 13 символам.
4. @Dan, возможно, я ошибаюсь, но я думал, что nvarchar (MAX) может хранить до 2 ГБ данных.
5. Похоже, вы правы, на самом деле. Не могли бы вы попробовать вызвать функцию со строкой, состоящей из множества элементов из 1 символа:
a,a,a,a,a,a,a ...
? Просто чтобы посмотреть, отключается ли она по-прежнему после 280 элементов.
Ответ №1:
Вот XML-решение, о котором я говорил…
Мне неясно, как вы генерируете свою @string
переменную, но этот метод нужно будет изменить.
Начните свой @string
с <M>
и завершите на </M>
. Тогда вместо того, чтобы использовать ‘,’ для разделения ваших значений, используйте </M><M>
вместо этого.
Я протестировал это в SSMS, скопировав / вставив мое строковое значение и удерживая вставку, пока номер моей строки не превысил 20 КБ. Первая LEN()
фактически вернула длину более 19 КБ. Я также показываю datalength()
часть xml, которая предназначена только для демонстрации.
Я использую этот метод для обработки данных в столбце типа csv в базе данных приложений поставщиков и превращаю его в столбец полезных данных. Я также использовал метод, который вы показываете выше. Этот метод намного быстрее, чем любой другой, который я пробовал.
Редактировать: Если это не помогло — расскажите нам подробнее. Как вы генерируете этот @string, может быть, мы можем предложить лучший вариант, чем заполнять его в строке csv.
Надеюсь, это поможет, вот оно:
DECLARE @string XML
SET @string = '<M>hello</M><M>world</M><M>hello</M><M>world</M><M>hello</M><M>world</M>'
SELECT LEN(CAST(@string AS NVARCHAR(MAX)))
SELECT DATALENGTH(@string)
SELECT Split.a.value('.', 'VARCHAR(MAX)') AS StringVal
FROM (SELECT @string AS String) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
--WHERE LEN(Split.a.value('.', 'VARCHAR(MAX)'))>1
Вот как это могло бы работать, если вашей исходной переменной является VARCHAR, на случай, если другие люди столкнутся с этим ответом в будущем.
declare @string2 varchar(max)
set @string2 = 'hello,world,hello,world,hello,world'
SELECT Split.a.value('.', 'VARCHAR(max)') AS String
FROM (SELECT CAST ('<M>' REPLACE(CAST(@string2 AS VARCHAR(MAX)), ',', '</M><M>') '</M>' AS XML) AS String) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
--WHERE LEN(Split.a.value('.', 'VARCHAR(max)'))>1
Если бы вы хранили csv в столбце, вы бы использовали что-то вроде этого:
SELECT DISTINCT A.UserID,
Split.a.value('.', 'VARCHAR(max)') AS String
FROM (SELECT UserID,
CAST ('<M>' REPLACE(CAST(someCSVListColumn AS VARCHAR), ',', '</M><M>') '</M>' AS XML) AS String
FROM #someTable) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
--WHERE LEN(Split.a.value('.', 'VARCHAR(max)'))>1
Комментарии:
1. извините, не видел этого вначале, просто попробовал это в базе данных сегодня, это работает действительно хорошо, большое вам спасибо. Еще одна вещь: как передать этот XML-параметр в c #? com. Parameters.Add(«@projectlist», SqlDbType.NVarChar). Значение = test, должен ли я просто изменить nvarchar на xml?
2. Я не специалист по c #, но я верю, что это может вам помочь aspdotnet-suresh.com/2012/12 /…
Ответ №2:
Попробуйте эту функцию… У меня нет времени проверять логику вашей функции..
Create FUNCTION [dbo].[UDF_Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (ID int Identity(1,1),Value varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Value) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end