¿Cómo se cuenta el número de apariciones de una determinada subcadena en un SQL varchar?

Tengo una columna que tiene valores formateados como a, b, c, d. ¿Hay alguna manera de contar el número de comas en ese valor en T-SQL?

La primera forma en que se me ocurre es hacerlo indirectamente reemplazando la coma con una cadena vacía y comparando las longitudes

Declare @string varchar(1000) Set @string = 'a,b,c,d' select len(@string) - len(replace(@string, ',', '')) 

Extensión rápida de la respuesta de cmsjr que funciona para cadenas de más de más caracteres.

 CREATE FUNCTION dbo.CountOccurancesOfString ( @searchString nvarchar(max), @searchTerm nvarchar(max) ) RETURNS INT AS BEGIN return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm) END 

Uso:

 SELECT * FROM MyTable where dbo.CountOccurancesOfString(MyColumn, 'MyString') = 1 

Puede comparar la longitud de la cadena con una donde se eliminan las comas:

 len(value) - len(replace(value,',','')) 

La respuesta de @csmjr tiene un problema en algunos casos.

Su respuesta fue hacer esto:

 Declare @string varchar(1000) Set @string = 'a,b,c,d' select len(@string) - len(replace(@string, ',', '')) 

Esto funciona en la mayoría de los escenarios, sin embargo, intente ejecutar esto:

 DECLARE @string VARCHAR(1000) SET @string = 'a,b,c,d ,' SELECT LEN(@string) - LEN(REPLACE(@string, ',', '')) 

Por alguna razón, REPLACE elimina la coma final, pero TAMBIÉN el espacio justo antes (no estoy seguro de por qué). Esto da como resultado un valor devuelto de 5 cuando se espera 4. Aquí hay otra forma de hacer esto que funcionará incluso en este escenario especial:

 DECLARE @string VARCHAR(1000) SET @string = 'a,b,c,d ,' SELECT LEN(REPLACE(@string, ',', '**')) - LEN(@string) 

Tenga en cuenta que no necesita usar asteriscos. Cualquier reemplazo de dos caracteres servirá. La idea es alargar la cuerda con un carácter para cada instancia del personaje que está contando, luego restar la longitud del original. Básicamente es el método opuesto a la respuesta original que no viene con el extraño efecto secundario de recorte.

 Declare @string varchar(1000) DECLARE @SearchString varchar(100) Set @string = 'as as df df as as as' SET @SearchString = 'as' select ((len(@string) - len(replace(@string, @SearchString, ''))) -(len(@string) - len(replace(@string, @SearchString, ''))) % 2) / len(@SearchString) 

Basándose en la solución de @ Andrew, obtendrá un rendimiento mucho mejor utilizando una función de valor de tabla no procesal y CROSS APPLY:

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Usage: SELECT t.[YourColumn], c.StringCount FROM YourDatabase.dbo.YourTable t CROSS APPLY dbo.CountOccurrencesOfString('your search string', t.[YourColumn]) c */ CREATE FUNCTION [dbo].[CountOccurrencesOfString] ( @searchTerm nvarchar(max), @searchString nvarchar(max) ) RETURNS TABLE AS RETURN SELECT (DATALENGTH(@searchString)-DATALENGTH(REPLACE(@searchString,@searchTerm,'')))/NULLIF(DATALENGTH(@searchTerm), 0) AS StringCount 

La respuesta aceptada es correcta, extendiéndola para usar 2 o más caracteres en la subcadena:

 Declare @string varchar(1000) Set @string = 'aa,bb,cc,dd' Set @substring = 'aa' select (len(@string) - len(replace(@string, @substring, '')))/len(@substring) 
 DECLARE @records varchar(400) SELECT @records = 'a,b,c,d' select LEN(@records) as 'Before removing Commas' , LEN(@records) - LEN(REPLACE(@records, ',', '')) 'After Removing Commans' 

Darrel Lee, creo que tiene una muy buena respuesta. Reemplace CHARINDEX() con PATINDEX() , y también puede realizar algunas búsquedas débiles de regex largo de una cadena …

Como, digamos que usas esto para @pattern :

 set @pattern='%[-.|!,'+char(9)+']%' 

¿Por qué querrías hacer algo tan loco como este?

Digamos que está cargando cadenas de texto delimitadas en una tabla de etapas, donde el campo que contiene los datos es algo así como varchar (8000) o nvarchar (max) …

A veces es más fácil / rápido hacer ELT (Extraer-Cargar-Transformar) con datos en lugar de ETL (Extraer-Transformar-Cargar), y una forma de hacerlo es cargar los registros delimitados tal como están en una tabla de etapas, especialmente si Es posible que desee una forma más simple de ver los registros excepcionales en lugar de tratarlos como parte de un paquete de SSIS … pero esa es una guerra santa para un hilo diferente.

Lo siguiente debería ser el truco para las búsquedas de un solo personaje y múltiples personajes:

 CREATE FUNCTION dbo.CountOccurrences ( @SearchString VARCHAR(1000), @SearchFor VARCHAR(1000) ) RETURNS TABLE AS RETURN ( SELECT COUNT(*) AS Occurrences FROM ( SELECT ROW_NUMBER() OVER (ORDER BY O.object_id) AS n FROM sys.objects AS O ) AS N JOIN ( VALUES (@SearchString) ) AS S (SearchString) ON SUBSTRING(S.SearchString, Nn, LEN(@SearchFor)) = @SearchFor ); GO --------------------------------------------------------------------------------------- -- Test the function for single and multiple character searches --------------------------------------------------------------------------------------- DECLARE @SearchForComma VARCHAR(10) = ',', @SearchForCharacters VARCHAR(10) = 'de'; DECLARE @TestTable TABLE ( TestData VARCHAR(30) NOT NULL ); INSERT INTO @TestTable ( TestData ) VALUES ('a,b,c,de,de ,d e'), ('abc,de,hijk,,'), (',,a,b,cde,,'); SELECT TT.TestData, CO.Occurrences AS CommaOccurrences, CO2.Occurrences AS CharacterOccurrences FROM @TestTable AS TT OUTER APPLY dbo.CountOccurrences(TT.TestData, @SearchForComma) AS CO OUTER APPLY dbo.CountOccurrences(TT.TestData, @SearchForCharacters) AS CO2; 

La función se puede simplificar un poco usando una tabla de números (dbo.Nums):

  RETURN ( SELECT COUNT(*) AS Occurrences FROM dbo.Nums AS N JOIN ( VALUES (@SearchString) ) AS S (SearchString) ON SUBSTRING(S.SearchString, Nn, LEN(@SearchFor)) = @SearchFor ); 

Si sabemos que hay una limitación en el LEN y el espacio, ¿por qué no podemos reemplazar el espacio primero? Entonces sabemos que no hay espacio para confundir a LEN.

 len(replace(@string, ' ', '-')) - len(replace(replace(@string, ' ', '-'), ',', '')) 

Usa este código, está funcionando perfectamente. He creado una función sql que acepta dos parámetros, el primer parámetro es la cadena larga que queremos buscar en él, y puede aceptar una longitud de cadena de hasta 1500 caracteres (por supuesto, puede ampliarlo o incluso cambiarlo a tipo de datos de texto) ) Y el segundo parámetro es la subcadena en la que queremos calcular el número de su aparición (su longitud es de hasta 200 caracteres, por supuesto puede cambiarla según sus necesidades). y el resultado es un número entero, representa el número de frecuencia ….. disfrútalo.


 CREATE FUNCTION [dbo].[GetSubstringCount] ( @InputString nvarchar(1500), @SubString NVARCHAR(200) ) RETURNS int AS BEGIN declare @K int , @StrLen int , @Count int , @SubStrLen int set @SubStrLen = (select len(@SubString)) set @Count = 0 Set @k = 1 set @StrLen =(select len(@InputString)) While @K <= @StrLen Begin if ((select substring(@InputString, @K, @SubStrLen)) = @SubString) begin if ((select CHARINDEX(@SubString ,@InputString)) > 0) begin set @Count = @Count +1 end end Set @K=@k+1 end return @Count end 

Puede usar el siguiente procedimiento almacenado para recuperar valores.

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_parsedata]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[sp_parsedata] GO create procedure sp_parsedata (@cid integer,@st varchar(1000)) as declare @coid integer declare @c integer declare @c1 integer select @c1=len(@st) - len(replace(@st, ',', '')) set @c=0 delete from table1 where complainid=@cid; while (@c<=@c1) begin if (@c<@c1) begin select @coid=cast(replace(left(@st,CHARINDEX(',',@st,1)),',','') as integer) select @st=SUBSTRING(@st,CHARINDEX(',',@st,1)+1,LEN(@st)) end else begin select @coid=cast(@st as integer) end insert into table1(complainid,courtid) values(@cid,@coid) set @c=@c+1 end 

La prueba Replace / Len es linda, pero probablemente muy ineficiente (especialmente en términos de memoria). Una función simple con un bucle hará el trabajo.

 CREATE FUNCTION [dbo].[fn_Occurences] ( @pattern varchar(255), @expression varchar(max) ) RETURNS int AS BEGIN DECLARE @Result int = 0; DECLARE @index BigInt = 0 DECLARE @patLen int = len(@pattern) SET @index = CHARINDEX(@pattern, @expression, @index) While @index > 0 BEGIN SET @Result = @Result + 1; SET @index = CHARINDEX(@pattern, @expression, @index + @patLen) END RETURN @Result END 

Tal vez no deberías almacenar datos de esa manera. Es una mala práctica almacenar una lista delimitada por comas en un campo. TI es muy ineficiente para consultar. Esta debe ser una tabla relacionada.