Servidor SQL: encuentra la n. ° aparición en una cadena

Tengo una columna de tabla que contiene valores como abc_1_2_3_4.gif o zzz_12_3_3_45.gif etc.

Quiero encontrar el índice de cada carácter de subrayado _ en los valores anteriores. Solo habrá cuatro guiones bajos, pero dado que pueden estar en cualquier posición de la cuerda, ¿cómo puedo lograr esto?

Probé la función subcadena y charindex , pero solo puedo obtener de manera confiable la primera. ¿Algunas ideas?

Una forma (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 

Actualizar

 ;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 

Puede usar la siguiente function para split the values por un delimiter . ¡Devolverá return a table y para encontrar la enésima aparición solo haga una select ! O cámbialo un poco para que te return lo que necesitas en lugar de la 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 

Puede usar CHARINDEX y especificar la ubicación de inicio:

 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 

Puede usar la misma función dentro para la posición +1

 charindex('_', [TEXT], (charindex('_', [TEXT], 1))+1) 

en donde +1 es la enésima vez que querrás encontrar.

 DECLARE @str AS VARCHAR(100) SET @str='1,2 , 3, 4, 5,6' SELECT COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[1]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[2]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[3]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[4]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[5]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[6]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[7]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[8]', 'varchar(128)')), ''), COALESCE(LTRIM(CAST((''+REPLACE(@str,',' ,'')+'') AS XML).value('(/X)[9]', 'varchar(128)')), '') 

Puede buscar los cuatro guiones bajos de esta manera :

 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 

puedes probarlo aquí.

O de esta manera simple:

 select charindex( '_', t ) as first, charindex( '_', t, charindex( '_', t ) + 1 ) as second, ... from #test 

Puedes intentar pelar la variable / matriz, asumiendo la distinción en tu lista

 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 @CURVAL=@VALUE ---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 
 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) 

Mi SQL admite la función de un substring_Index donde devolverá la posición de un valor en una cadena para el n occurance. Se podría escribir una función similar definida por el usuario para lograr esto. Ejemplo en el enlace

Alternativamente, puede usar la función charindex. Llámelo x veces para informar la ubicación de cada _ dada una posición de inicio +1 de la instancia encontrada previamente. hasta que se encuentre un 0

Editar: NM Charindex es la función correcta

Lo hice creando varias funciones personalizadas separadas, una para cada posición del personaje buscado, es decir, 2º, 3º:

CREATE FUNCTION [dbo]. [FnCHARPOS2] (@SEARCHCHAR VARCHAR (255), @SEARCHSTRING VARCHAR (255)) DEVUELVE INT COMO INICIAR RETORNO 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); 

A continuación, puede pasar como parámetro el carácter que está buscando y la cadena que está buscando:

Entonces, si buscabas ‘f’ y querías saber la posición de las 1ras 3 ocurrencias:

 select database.dbo.fnCHARPOS2('f',tablename.columnname), database.dbo.fnCHARPOS3('f',tablename.columnname) from tablename 

¡Funcionó para mí!

Decidí usar una función recursiva porque para mí era más fácil seguir la lógica. Tenga en cuenta que SQL Server tiene un límite de recursión de funciones predeterminado de 32, por lo que esto solo es bueno para cargas de trabajo más pequeñas.

 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 

He utilizado una función para tomar el elemento “enésimo” de un campo de cadena delimitado con gran éxito. Como se mencionó anteriormente, no es una manera “rápida” de lidiar con las cosas, pero seguro que es muy conveniente.

 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'' + replace(@delimited, @delimiter,'') + '' select @result = r.value('.','varchar(max)') from @xml.nodes('//root/r[sql:variable("@index")]') as records(r) return @result end 

Una muestra simple para hacer esto con la conversión xml:

 SELECT 'A|B|C' , concat('', REPLACE('A|B|C', '|', ''), '') , cast(concat('', REPLACE('A|B|C', '|', ''), '') as xml).query('/x[2]') , cast(concat('', REPLACE('A|B|C', '|', ''), '') as xml).value('/x[2]', 'varchar'); 

Y aquí una traducción para su muestra:

 SELECT gifname ,cast(concat('', REPLACE(gifname, '_', ''), '') as xml).query('/x[2]') as xmlelement , cast(concat('', REPLACE(gifname, '_', ''), '') 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 

He hecho algo similar en SQL Server usando PATINDEX y un conjunto Regex CLR que devuelve una matriz de valores. Si quieres probarlo, puedo subir una muestra cuando llegue al trabajo.

Estaba jugando con una forma más rápida de hacer esto que simplemente iterar a través de la cadena.

 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 

Verifica el uso de parsename como una alternativa potencial de esta publicación stackexchange.

https://dba.stackexchange.com/questions/42393/get-the-2nd-or-3rd-occurrence-of-a-value-in-a-delimited-string

 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 '+@character end 
 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 ABC MS SQL Server