SQL varchar에서 특정 하위 문자열의 발생 횟수를 어떻게 계산합니까?
a, b, c, d와 같은 형식의 값이 있는 열이 있습니다.T-SQL에서 그 값의 쉼표 수를 셀 수 있는 방법이 있나요?
가장 먼저 떠오르는 방법은 콤마를 빈 문자열로 대체하고 길이를 비교하는 것입니다.
Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))
여러 문자가 있는 문자열에 대해 작동하는 cmsjr의 응답의 빠른 확장입니다.
CREATE FUNCTION dbo.CountOccurrencesOfString
(
@searchString nvarchar(max),
@searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END
사용방법:
SELECT * FROM MyTable
where dbo.CountOccurrencesOfString(MyColumn, 'MyString') = 1
문자열의 길이를 콤마가 삭제된 문자열 길이와 비교할 수 있습니다.
len(value) - len(replace(value,',',''))
경우에 따라서는 @csmjr의 응답에 문제가 있습니다.
그의 대답은 이렇게 하는 것이었다.
Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))
이것은 대부분의 시나리오에서 기능하지만, 다음을 실행해 보십시오.
DECLARE @string VARCHAR(1000)
SET @string = 'a,b,c,d ,'
SELECT LEN(@string) - LEN(REPLACE(@string, ',', ''))
어떤 이유로 REPLACE는 마지막 쉼표는 물론 쉼표 바로 앞에 있는 공간도 삭제합니다(이유는 확실하지 않습니다).그러면 4를 예상할 때 반환되는 값이 5가 됩니다.이 특수한 시나리오에서도 동작하는 다른 방법을 다음에 나타냅니다.
DECLARE @string VARCHAR(1000)
SET @string = 'a,b,c,d ,'
SELECT LEN(REPLACE(@string, ',', '**')) - LEN(@string)
아스타리스크는 사용할 필요가 없습니다.2글자 치환이면 됩니다.이 아이디어는 셀 문자의 인스턴스마다 문자열을 1자씩 길게 한 다음 원본의 길이를 빼는 것입니다.그것은 기본적으로 원래의 답변과는 정반대의 방법으로, 이상한 트리밍의 부작용이 수반되지 않습니다.
@Andrew의 솔루션을 기반으로 프로시저 이외의 테이블 밸류 함수와 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
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)
승인된 답변이 올바릅니다.서브스트링에 2자 이상의 문자를 사용하도록 확장됩니다.
Declare @string varchar(1000)
Set @string = 'aa,bb,cc,dd'
Set @substring = 'aa'
select (len(@string) - len(replace(@string, @substring, '')))/len(@substring)
대럴 리CHARINDEX()PATINDEX() 을 할 수 있습니다.regex★★★★★★★★★★★★★★★★...
를 들어, 이걸 요, 사용하다, 사용한다고 하면요.@pattern:
set @pattern='%[-.|!,'+char(9)+']%'
왜 이런 미친 짓을 하고 싶어하죠?
구분된 텍스트 문자열을 스테이징 테이블에 로드한다고 가정합니다. 여기서 데이터를 저장하는 필드는 varchar(8000) 또는 nvarchar(max)와 같습니다.
ETL(Extract-Transform-Load)보다 데이터를 사용하여 ELT(Extract-Load-Transform)를 실행하는 것이 더 쉽고 빠른 경우가 있습니다.이 방법 중 하나는 구분된 레코드를 그대로 스테이징 테이블에 로드하는 것입니다.특히 SS 패키지의 일부로서 취급하는 것보다 예외 레코드를 참조하는 간단한 방법을 찾는 것입니다.하지만 그건 다른 실타래를 위한 성전이야
LEN과 공간에 제한이 있다는 것을 알고 있다면 먼저 공간을 교체할 수 없는 이유는 무엇입니까?그러면 LEN을 혼란스럽게 할 여지가 없다는 것을 알 수 있습니다.
len(replace(@string, ' ', '-')) - len(replace(replace(@string, ' ', '-'), ',', ''))
이 코드를 사용하면 완벽하게 작동합니다.두 개의 파라미터를 받아들이는 SQL 함수를 만들었습니다.첫 번째 파라미터는 검색하는 긴 문자열입니다.이 함수는 최대 1500자의 문자열 길이를 수용할 수 있습니다(물론 확장하거나 텍스트 데이터 형식으로 변경할 수도 있습니다).두 번째 파라미터는 발생횟수를 계산하고 싶은 서브스트링입니다(최대 200자까지입니다.필요에 따라 변경할 수 있습니다).그리고 출력은 정수이며, 주파수 수를 나타냅니다. ...이 값을 구합니다.
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
SQL 2017 이상에서는 다음을 사용할 수 있습니다.
declare @hits int = 0
set @hits = (select value from STRING_SPLIT('F609,4DFA,8499',','));
select count(@hits)
상위 답변 및 기타 답변을 기반으로 향상된 버전:
문자열을 딜리미터로 감싸면 LEN이 올바르게 동작합니다.치환 문자열을 일치 문자열보다1글자 길게 하면 분할할 필요가 없어집니다.
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'
다음은 단일 문자 검색과 다중 문자 검색 모두에 대해 기능을 수행합니다.
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, N.n, 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;
이 기능은 숫자표(dbo)를 사용하여 약간 단순화할 수 있습니다.수):
RETURN (
SELECT COUNT(*) AS Occurrences
FROM dbo.Nums AS N
JOIN (
VALUES (@SearchString)
) AS S (SearchString)
ON
SUBSTRING(S.SearchString, N.n, LEN(@SearchFor)) = @SearchFor
);
마지막으로 입력에 문자 접두사와 접미사를 추가하여 가능한 모든 상황을 커버하는 함수를 작성합니다.이 문자는 검색 매개 변수에 지정된 문자와 다르므로 결과에 영향을 미치지 않습니다.
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
사용.
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)
이 T-SQL 코드는 문장 @s에서 패턴 @p의 모든 발생을 찾아서 출력합니다. 나중에 문장에 대한 어떠한 처리도 가능합니다.
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
결과는 다음과 같습니다: 1 6 13 20
결국 CTE 테이블을 사용하게 되었습니다.
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]
이 기능을 사용하면 텍스트의 반복 횟수를 얻을 수 있습니다.
/****** 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
레퍼런스 https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
예:
SELECT s.*
,s.[Number1] - (SELECT COUNT(Value)
FROM string_split(s.[StringColumn],',')
WHERE RTRIM(VALUE) <> '')
FROM TableName AS s
대상: SQL Server 2016 (13.x) 이후
다음 저장 프로시저를 사용하여 , 값을 가져올 수 있습니다.
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
Replace/Len 테스트는 귀엽지만 매우 비효율적일 수 있습니다(특히 메모리의 경우).루프가 있는 단순한 기능으로 충분합니다.
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
데이터를 그런 식으로 저장해서는 안 될 수도 있습니다.필드에 쉼표로 구분된 목록을 저장하는 것은 잘못된 방법입니다.IT는 쿼리에 매우 비효율적입니다.이것은 관련된 표여야 합니다.
언급URL : https://stackoverflow.com/questions/738282/how-do-you-count-the-number-of-occurrences-of-a-certain-substring-in-a-sql-varch
'programing' 카테고리의 다른 글
| 두 열의 조합에 고유한 제약 조건 추가 (0) | 2023.04.07 |
|---|---|
| SQL Server에서의 LIKE vs Contains (0) | 2023.04.07 |
| C#을 사용하여 텍스트에 탭 문자 삽입 (0) | 2023.04.07 |
| SQL 서버에서 행을 열로 효율적으로 변환 (0) | 2023.04.07 |
| 시간을 고려하지 않고 날짜 시간 열별로 그룹화하는 방법 (0) | 2023.04.07 |