I have a column that has values formatted like a,b,c,d. Is there a way to count the number of commas in that value in T-SQL?
23 Answers
The first way that comes to mind is to do it indirectly by replacing the comma with an empty string and comparing the lengths
Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))
You can compare the length of the string with one where the commas are removed:
len(value) - len(replace(value,',',''))
Accepted answer is correct , extending it to use 2 or more character in substring:
Declare @string varchar(1000)
Set @string = 'aa,bb,cc,dd'
Set @substring = 'aa'
select (len(@string) - len(replace(@string, @substring, '')))/len(@substring)
If we know there is a limitation on LEN and space, why cant we replace the space first? Then we know there is no space to confuse LEN.
len(replace(@string, ' ', '-')) - len(replace(replace(@string, ' ', '-'), ',', ''))
Use this code, it is working perfectly. I have create a sql function that accept two parameters, the first param is the long string that we want to search into it,and it can accept string length up to 1500 character(of course you can extend it or even change it to text datatype). And the second parameter is the substring that we want to calculate the number of its occurance(its length is up to 200 character, of course you can change it to what your need). and the output is an integer, represent the number of frequency.....enjoy it.
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
In SQL 2017 or higher, you can use this:
declare @hits int = 0
set @hits = (select value from STRING_SPLIT('F609,4DFA,8499',','));
select count(@hits)
Improved version based on top answer and other answers:
Wrapping the string with delimiters ensures that LEN works properly. Making the replace character string one character longer than the match string removes the need for division.
CREATE FUNCTION dbo.MatchCount(@value nvarchar(max), @match nvarchar(max))
RETURNS int
BEGIN
RETURN LEN('[' + REPLACE(@value,@match,REPLICATE('*', LEN('[' + @match + ']') - 1)) + ']') - LEN('['+@value+']')
END
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'
I finally write this function that should cover all the possible situations, adding a char prefix and suffix to the input. this char is evaluated to be different to any of the char conteined in the search parameter, so it can't affect the result.
CREATE FUNCTION [dbo].[CountOccurrency]
(
@Input nvarchar(max),
@Search nvarchar(max)
)
RETURNS int AS
BEGIN
declare @SearhLength as int = len('-' + @Search + '-') -2;
declare @conteinerIndex as int = 255;
declare @conteiner as char(1) = char(@conteinerIndex);
WHILE ((CHARINDEX(@conteiner, @Search)>0) and (@conteinerIndex>0))
BEGIN
set @conteinerIndex = @conteinerIndex-1;
set @conteiner = char(@conteinerIndex);
END;
set @Input = @conteiner + @Input + @conteiner
RETURN (len(@Input) - len(replace(@Input, @Search, ''))) / @SearhLength
END
usage
select dbo.CountOccurrency('a,b,c,d ,', ',')
Declare @MainStr nvarchar(200)
Declare @SubStr nvarchar(10)
Set @MainStr = 'nikhildfdfdfuzxsznikhilweszxnikhil'
Set @SubStr = 'nikhil'
Select (Len(@MainStr) - Len(REPLACE(@MainStr,@SubStr,'')))/Len(@SubStr)
this T-SQL code finds and prints all occurrences of pattern @p in sentence @s. you can do any processing on the sentence afterward.
declare @old_hit int = 0
declare @hit int = 0
declare @i int = 0
declare @s varchar(max)='alibcalirezaalivisualization'
declare @p varchar(max)='ali'
while @i<len(@s)
begin
set @hit=charindex(@p,@s,@i)
if @hit>@old_hit
begin
set @old_hit =@hit
set @i=@hit+1
print @hit
end
else
break
end
the result is: 1 6 13 20
I ended up using a CTE table for this,
CREATE TABLE #test (
[id] int,
[field] nvarchar(500)
)
INSERT INTO #test ([id], [field])
VALUES (1, 'this is a test string http://url, and https://google.com'),
(2, 'another string, hello world http://example.com'),
(3, 'a string with no url')
SELECT *
FROM #test
;WITH URL_count_cte ([id], [url_index], [field])
AS
(
SELECT [id], CHARINDEX('http', [field], 0)+1 AS [url_index], [field]
FROM #test AS [t]
WHERE CHARINDEX('http', [field], 0) != 0
UNION ALL
SELECT [id], CHARINDEX('http', [field], [url_index])+1 AS [url_index], [field]
FROM URL_count_cte
WHERE CHARINDEX('http', [field], [url_index]) > 0
)
-- total urls
SELECT COUNT(1)
FROM URL_count_cte
-- urls per row
SELECT [id], COUNT(1) AS [url_count]
FROM URL_count_cte
GROUP BY [id]
Using this function, you can get the number of repetitions of words in a text.
/****** Object: UserDefinedFunction [dbo].[fn_getCountKeywords] Script Date: 22/11/2021 17:52:00 ******/
DROP FUNCTION IF EXISTS [dbo].[fn_getCountKeywords]
GO
/****** Object: UserDefinedFunction [dbo].[fn_getCountKeywords] Script Date: 2211/2021 17:52:00 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: m_Khezrian
-- Create date: 2021/11/22-17:52
-- Description: Return Count Keywords In Input Text
-- =============================================
Create OR Alter Function [dbo].[fn_getCountKeywords]
(@Text nvarchar(max)
,@Keywords nvarchar(max)
)
RETURNS @Result TABLE
(
[ID] int Not Null IDENTITY PRIMARY KEY
,[Keyword] nvarchar(max) Not Null
,[Cnt] int Not Null Default(0)
)
/*With ENCRYPTION*/ As
Begin
Declare @Key nvarchar(max);
Declare @Cnt int;
Declare @I int;
Set @I = 0 ;
--Set @Text = QUOTENAME(@Text);
Insert Into @Result
([Keyword])
Select Trim([value])
From String_Split(@Keywords,N',')
Group By [value]
Order By Len([value]) Desc;
Declare CntKey_Cursor Insensitive Cursor For
Select [Keyword]
From @Result
Order By [ID];
Open CntKey_Cursor;
Fetch Next From CntKey_Cursor Into @Key;
While (@@Fetch_STATUS = 0) Begin
Set @Cnt = 0;
While (PatIndex(N'%'+@Key+'%',@Text) > 0) Begin
Set @Cnt += 1;
Set @I += 1 ;
Set @Text = Stuff(@Text,PatIndex(N'%'+@Key+'%',@Text),len(@Key),N'{'+Convert(nvarchar,@I)+'}');
--Set @Text = Replace(@Text,@Key,N'{'+Convert(nvarchar,@I)+'}');
End--While
Update @Result
Set [Cnt] = @Cnt
Where ([Keyword] = @Key);
Fetch Next From CntKey_Cursor Into @Key;
End--While
Close CntKey_Cursor;
Deallocate CntKey_Cursor;
Return
End
GO
--Test
Select *
From dbo.fn_getCountKeywords(
N'<U+0001F4E3> MARKET IMPACT Euro area Euro CPIarea annual inflation up to 3.0% MaCPIRKET forex'
,N'CPI ,core,MaRKET , Euro area'
)
Go
Reference https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
Example:
SELECT s.*
,s.[Number1] - (SELECT COUNT(Value)
FROM string_split(s.[StringColumn],',')
WHERE RTRIM(VALUE) <> '')
FROM TableName AS s
Applies to: SQL Server 2016 (13.x) and later
Perhaps you should not store data that way. It is a bad practice to ever store a comma delimited list in a field. IT is very inefficient for querying. This should be a related table.
Related