SQL Server - найти n-ое вхождение в строку
У меня есть столбец таблицы, который содержит такие значения, как abc_1_2_3_4.gif или zzz_12_3_3_45.gif и т.д.
Я хочу найти индекс каждого подчеркивания _ в приведенных выше значениях. Там будет только четыре подчеркивания, но учитывая, что они могут находиться в любом положении в строке, как я могу это достичь?
Я пробовал подстроку и charindex, но я могу только надежно завладеть первым. Любые идеи?
Ответы
Ответ 1
Один способ (2k8);
select 'abc_1_2_3_4.gif ' as img into #T
insert #T values ('zzz_12_3_3_45.gif')
;with T as (
select 0 as row, charindex('_', img) pos, img from #T
union all
select pos + 1, charindex('_', img, pos + 1), img
from T
where pos > 0
)
select
img, pos
from T
where pos > 0
order by img, pos
>>>>
img pos
abc_1_2_3_4.gif 4
abc_1_2_3_4.gif 6
abc_1_2_3_4.gif 8
abc_1_2_3_4.gif 10
zzz_12_3_3_45.gif 4
zzz_12_3_3_45.gif 7
zzz_12_3_3_45.gif 9
zzz_12_3_3_45.gif 11
Обновление
;with T(img, starts, pos) as (
select img, 1, charindex('_', img) from #t
union all
select img, pos + 1, charindex('_', img, pos + 1)
from t
where pos > 0
)
select
*, substring(img, starts, case when pos > 0 then pos - starts else len(img) end) token
from T
order by img, starts
>>>
img starts pos token
abc_1_2_3_4.gif 1 4 abc
abc_1_2_3_4.gif 5 6 1
abc_1_2_3_4.gif 7 8 2
abc_1_2_3_4.gif 9 10 3
abc_1_2_3_4.gif 11 0 4.gif
zzz_12_3_3_45.gif 1 4 zzz
zzz_12_3_3_45.gif 5 7 12
zzz_12_3_3_45.gif 8 9 3
zzz_12_3_3_45.gif 10 11 3
zzz_12_3_3_45.gif 12 0 45.gif
Ответ 2
Вы можете использовать ту же функцию внутри для позиции +1
charindex('_', [TEXT], (charindex('_', [TEXT], 1))+1)
где +1 - это n-й раз, когда вы захотите найти.
Ответ 3
Вы можете использовать CHARINDEX и указать начальное местоположение:
DECLARE @x VARCHAR(32) = 'MS-SQL-Server';
SELECT
STUFF(STUFF(@x,3 , 0, '/'), 8, 0, '/') InsertString
,CHARINDEX('-',LTRIM(RTRIM(@x))) FirstIndexOf
,CHARINDEX('-',LTRIM(RTRIM(@x)), (CHARINDEX('-', LTRIM(RTRIM(@x)) )+1)) SecondIndexOf
,CHARINDEX('-',@x,CHARINDEX('-',@x, (CHARINDEX('-',@x)+1))+1) ThirdIndexOf
,CHARINDEX('-',REVERSE(LTRIM(RTRIM(@x)))) LastIndexOf;
GO
Ответ 4
Вы можете использовать function to split the values с помощью delimiter. Это будет return a table, и чтобы найти n-е вхождение, просто сделайте select на нем! Или немного измените для него return то, что вам нужно, вместо table.
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
Ответ 5
DECLARE @str AS VARCHAR(100)
SET @str='1,2 , 3, 4, 5,6'
SELECT COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[1]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[2]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[3]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[4]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[5]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[6]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[7]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[8]', 'varchar(128)')), ''),
COALESCE(LTRIM(CAST(('<X>'+REPLACE(@str,',' ,'</X><X>')+'</X>') AS XML).value('(/X)[9]', 'varchar(128)')), '')
Ответ 6
Вы можете искать четыре подчеркивания таким образом:
create table #test
( t varchar(50) );
insert into #test values
( 'abc_1_2_3_4.gif'),
('zzz_12_3_3_45.gif');
declare @t varchar(50);
declare @t_aux varchar(50);
declare @t1 int;
declare @t2 int;
declare @t3 int;
declare @t4 int;
DECLARE t_cursor CURSOR
FOR SELECT t FROM #test
OPEN t_cursor
FETCH NEXT FROM t_cursor into @t;
set @t1 = charindex( '_', @t )
set @t2 = charindex( '_', @t , @t1+1)
set @t3 = charindex( '_', @t , @t2+1)
set @t4 = charindex( '_', @t , @t3+1)
select @t1, @t2, t3, t4
--do a loop to iterate over all table
вы можете проверить его здесь.
Или простым способом:
select
charindex( '_', t ) as first,
charindex( '_', t, charindex( '_', t ) + 1 ) as second,
...
from
#test
Ответ 7
Вы можете попробовать очистить переменную/массив, предполагая отличимость в вашем списке
declare @array table ----table of values
(
id int identity(1,1)
,value nvarchar(max)
)
DECLARE @VALUE NVARCHAR(MAX)='val1_val2_val3_val4_val5_val6_val7'----string array
DECLARE @CURVAL NVARCHAR(MAX) ---current value
DECLARE @DELIM NVARCHAR(1)='_' ---delimiter
DECLARE @BREAKPT INT ---current index of the delimiter
WHILE EXISTS (SELECT @VALUE)
BEGIN
SET @BREAKPT=CHARINDEX(@DELIM,@VALUE) ---set the current index
---
If @BREAKPT<> 0 ---index at 0 breaks the loop
begin
SET @CURVAL=SUBSTRING(@VALUE,1,@BREAKPT-1) ---current value
set @VALUE=REPLACE(@VALUE,SUBSTRING(@VALUE,1,@BREAKPT),'') ---current value and delimiter, replace
insert into @array(value) ---insert data
select @CURVAL
end
else
begin
SET @[email protected] ---current value now last value
insert into @array(value) ---insert data
select @CURVAL
break ---break loop
end
end
select * from @array ---find nth occurance given the id
Ответ 8
DECLARE @LEN INT
DECLARE @VAR VARCHAR(20)
SET @VAR = 'HELLO WORLD'
SET @LEN = LEN(@VAR)
--SELECT @LEN
SELECT PATINDEX('%O%',SUBSTRING(@VAR,PATINDEX('%O%' ,@VAR) + 1 ,PATINDEX('%O%',@VAR) + 1)) + PATINDEX('%O%',@VAR)
Ответ 9
Мой SQL поддерживает функцию substring_Index, где он вернет позицию значения в строке для n события. Для достижения этой цели можно написать аналогичную функцию, определенную пользователем. Пример в ссылка
В качестве альтернативы вы можете использовать функцию charindex, чтобы вызвать ее каждые x, чтобы сообщить о местоположении каждого _, учитывая начальную позицию +1 ранее найденного экземпляра. пока не будет найдено 0
Изменить: NM Charindex - это правильная функция
Ответ 10
Я сделал это, создав несколько отдельных пользовательских функций, по одному для каждой позиции искомого символа, т.е. 2nd, 3rd:
СОЗДАТЬ ФУНКЦИЮ [dbo]. [fnCHARPOS2] (@SEARCHCHAR VARCHAR (255), @SEARCHSTRING VARCHAR (255)) ВОЗВРАЩАЕТ INT В ВИДЕ НАЧАТЬ RETURN CHARINDEX (@SEARCHCHAR, @SEARCHSTRING (CHARINDEX (@SEARCHCHAR, @SEARCHSTRING, 0) +1));
CREATE FUNCTION [dbo].[fnCHARPOS3]
(@SEARCHCHAR VARCHAR(255),
@SEARCHSTRING VARCHAR(255))
RETURNS INT
AS
BEGIN
RETURN CHARINDEX(@SEARCHCHAR,@SEARCHSTRING, (CHARINDEX(@SEARCHCHAR,@SEARCHSTRING, (CHARINDEX(@SEARCHCHAR,@SEARCHSTRING,0)+1)))+1);
Затем вы можете передать в качестве параметра символ, который вы ищете, и строку, которую вы ищете:
Итак, если вы искали "f" и хотели узнать положение 1-го 3-х случаев:
select
database.dbo.fnCHARPOS2('f',tablename.columnname),
database.dbo.fnCHARPOS3('f',tablename.columnname)
from tablename
Это сработало для меня!
Ответ 11
Я решил использовать рекурсивную функцию, потому что для меня было легче следовать логике. Обратите внимание, что SQL Server имеет ограничение рекурсии функции по умолчанию 32, поэтому это подходит только для небольших рабочих нагрузок.
create function dbo._charindex_nth (
@FindThis varchar(8000),
@InThis varchar(max),
@StartFrom int,
@NthOccurence tinyint
)
returns bigint
as
begin
/*
Recursive helper used by dbo.charindex_nth to return the position of the nth occurance of @FindThis in @InThis
Who When What
PJR 160421 Initial
*/
declare @Pos bigint
if isnull(@NthOccurence, 0) <= 0 or isnull(@StartFrom, 0) <= 0
begin
select @Pos = 0
end else begin
if @NthOccurence = 1
begin
select @Pos = charindex(@FindThis, @InThis, @StartFrom)
end else begin
select @Pos = dbo._charindex_nth(@FindThis, @InThis, nullif(charindex(@FindThis, @InThis, @StartFrom), 0) + 1, @NthOccurence - 1)
end
end
return @Pos
end
create function dbo.charindex_nth (
@FindThis varchar(8000),
@InThis varchar(max),
@NthOccurence tinyint
)
returns bigint
as
begin
/*
Returns the position of the nth occurance of @FindThis in @InThis
Who When What
PJR 160421 Initial
*/
return dbo._charindex_nth(@FindThis, @InThis, 1, @NthOccurence)
end
declare @val varchar(max) = 'zzz_12_3_3_45.gif'
select dbo.charindex_nth('_', @val, 1) Underscore1
, dbo.charindex_nth('_', @val, 2) Underscore2
, dbo.charindex_nth('_', @val, 3) Underscore3
, dbo.charindex_nth('_', @val, 4) Underscore4
Ответ 12
Я использовал функцию для захвата элемента "nth" из поля с разделителями строк с большим успехом. Как упоминалось выше, это не "быстрый" способ иметь дело с вещами, но он уверен, что это удобно.
create function GetArrayIndex(@delimited nvarchar(max), @index int, @delimiter nvarchar(100) = ',') returns nvarchar(max)
as
begin
declare @xml xml, @result nvarchar(max)
set @xml = N'<root><r>' + replace(@delimited, @delimiter,'</r><r>') + '</r></root>'
select @result = r.value('.','varchar(max)')
from @xml.nodes('//root/r[sql:variable("@index")]') as records(r)
return @result
end
Ответ 13
Простой пример для преобразования xml:
SELECT 'A|B|C'
, concat('<x>', REPLACE('A|B|C', '|', '</x><x>'), '</x>')
, cast(concat('<x>', REPLACE('A|B|C', '|', '</x><x>'), '</x>') as xml).query('/x[2]')
, cast(concat('<x>', REPLACE('A|B|C', '|', '</x><x>'), '</x>') as xml).value('/x[2]',
'varchar');
И вот перевод для вашего примера:
SELECT gifname
,cast(concat('<x>', REPLACE(gifname, '_', '</x><x>'), '</x>') as xml).query('/x[2]') as xmlelement
, cast(concat('<x>', REPLACE(gifname, '_', '</x><x>'), '</x>') as xml).value('/x[2]', 'varchar(10)') as result
FROM (
SELECT 'abc_1_2_3_4.gif' as gifname
UNION ALL
SELECT 'zzz_12_3_3_45.gif'
) tmp
Ответ 14
Я сделал что-то подобное в SQL Server с помощью PATINDEX и сборки CLR Regex, которая возвращает массив значений. Если вы хотите попробовать, я могу загрузить образец, когда я получу работу.
Ответ 15
Я играл с более быстрым способом сделать это, чем просто перебирать строку.
CREATE FUNCTION [ssf_GetNthSeparatorPosition] ( @TargetString VARCHAR(MAX)
, @Sep VARCHAR(25)
, @n INTEGER )
RETURNS INTEGER
/****************************************************************************************
--#############################################################################
-- Returns the position of the Nth Charactor sequence
-- 1234567890123456789
-- Declare @thatString varchar(max) = 'hi,there,jay,yo'
Select dbo.ssf_GetNthSeparatorPosition(@thatString, ',', 3) --would return 13
--############################################################################
****************************************************************************************/
AS
BEGIN
DECLARE @Retval INTEGER = 0
DECLARE @CurPos INTEGER = 0
DECLARE @LenSep INTEGER = LEN(@Sep)
SELECT @CurPos = CHARINDEX(@Sep, @TargetString)
IF ISNULL(@LenSep, 0) > 0
AND @CurPos > 0
BEGIN
SELECT @CurPos = 0
;with lv0 AS (SELECT 0 g UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5),
results
AS ( SELECT n - LEN(@Sep) AS Nth
, row_number() OVER ( ORDER BY n ) - 1 AS Position
FROM Tally t
WHERE n BETWEEN 1
AND DATALENGTH(@TargetString) + DATALENGTH(@Sep)
AND SUBSTRING(@Sep + @TargetString, n, LEN(@Sep)) = @Sep)
SELECT @CurPos = Nth
FROM results
WHERE results.Position = @n
END
RETURN @CurPos
END
GO
Ответ 16
Осмотрите использование parseame как потенциальную альтернативу из этой записи stackexchange.
https://dba.stackexchange.com/questions/42393/get-the-2nd-or-3rd-occurrence-of-a-value-in-a-delimited-string
Ответ 17
declare @a nvarchar(50)='Enter Your string '
declare @character char='e'
declare @nthoccurence int = 2
declare @i int = 1
declare @j int =0
declare @count int = len(@a)-len(replace(@a,@character,''))
if(@count >= @nthoccurence)
begin
while (@I <= @nthoccurence)
begin
set @j= CHARINDEX(@character,@a,@j+1)
set @i= @i+1
end
print @j
end
else
Print 'you have only '+convert(nvarchar ,@count)+' occurrences of '[email protected]
end
Ответ 18
DECLARE @x VARCHAR(32) = 'MS-SQL-Server';
SELECT
SUBSTRING(@x,0,CHARINDEX('-',LTRIM(RTRIM(@x)))) A,
SUBSTRING(@x,CHARINDEX('-',LTRIM(RTRIM(@x)))+1,CHARINDEX('-'
,LTRIM(RTRIM(@x)))) B,
SUBSTRING(@x,CHARINDEX('-',REVERSE(LTRIM(RTRIM(@x))))+1,LEN(@x)-1) C
A B C
MS SQL Server
Ответ 19
Вдохновленный ответом AlexK K Один из способов (2k8) я создал скрипт для функции токена для SQL Server для возврата определенного токена из строки. Мне это понадобилось для реорганизации пакета SSIS в T-SQL без необходимости вручную выполнять решение Алекса несколько раз. У моей функции есть один недостаток: она возвращает значение токена в виде таблицы (один столбец, одна строка), а не в качестве значения varchar. Если у кого-то есть решение для этого, пожалуйста, дайте мне знать.
DROP FUNCTION [RDW].[token]
GO
create function [RDW].[token] (@string varchar(8000), @split varchar(50), @returnIndex int)
returns table
as
return with T(img, starts, pos, [index]) as (
select @string, 1, charindex(@split, @string), 0
union all
select @string, pos + 1, charindex(@split, @string, pos + 1), [index]+1
from t
where pos > 0
)
select substring(img, starts, case when pos > 0 then pos - starts else len(img) end) token
from T
where [index] = @returnIndex
GO
Ответ 20
Я использовал MS access: я создал переменную поля для каждой позиции "-" в "компоненте", затем начал свой поиск instr при предыдущем появлении "-" +1, чтобы поиск instr не перекрывался.
Я использовал их в качестве ориентира для вычисления расстояния между каждым "-", чтобы вытащить нужную строку и заполнить ее соответствующим числом 0, чтобы создать формат "0000-0000-0000-0000" из строки различной длины между каждым "-",
''''AS orig, [Partlist Short Parts].component,
''''Format(Mid([component],1,[x]-1),"0000") &
''''Format(Mid([component],[x]+1,[x1]-[x]),"0000") &
''''Format(Mid([component],[x1]+1,[x2]-[x1]),"0000") & "-" &
''''Format(Mid([component],[x2]+1,4),"0000")
''''AS Xfinal, InStr(1,[component],"-")
''''AS x, InStr([x]+1,[component],"-")
''''AS x1, InStr([x1]+1,[component],"-")
''''AS x2 INTO TheFixedPartFormat
''''FROM [Partlist Short Parts];
