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)
별표를 사용할 필요는 없습니다. 두 문자로 대체됩니다. 계산하는 캐릭터의 각 인스턴스에 대해 문자열을 한 문자 씩 늘리고 원본의 길이를 빼는 것이 좋습니다. 기본적으로 이상한 트리밍 부작용이없는 원래 답변의 반대 방법입니다.
@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)
LEN과 공간에 제한이 있다는 것을 알고 있다면 왜 공간을 먼저 대체 할 수 없습니까? 그런 다음 LEN을 혼동 할 공간이 없다는 것을 알고 있습니다.
len(replace(@string, ' ', '-')) - len(replace(replace(@string, ' ', '-'), ',', ''))
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'
대럴 리 꽤 좋은 답변이 있다고 생각합니다. 교체 CHARINDEX()로 PATINDEX(), 당신은 몇 가지 약을 할 수 regex도 캐릭터에 따라 검색을 ...
마찬가지로 이것을 사용한다고 가정 해보십시오 @pattern.
set @pattern='%[-.|!,'+char(9)+']%'
왜 이런 미친 짓을하고 싶습니까?
구분 된 텍스트 문자열을 준비 테이블에로드한다고 가정합니다. 여기서 데이터를 보유하는 필드는 varchar (8000) 또는 nvarchar (max)와 같습니다.
때때로 ETL (Extract-Transform-Load) 대신 데이터를 사용하여 ELT (Extract-Load-Transform)를 수행하는 것이 더 쉽고 빠르며,이를 수행하는 한 가지 방법은 구분 된 레코드를 스테이징 테이블에 그대로로드하는 것입니다. SSIS 패키지의 일부로 다루지 않고 예외적 인 레코드를 볼 수있는 더 간단한 방법을 원할 수도 있지만 다른 스레드와의 전쟁입니다.
다음은 단일 문자 검색과 여러 문자 검색에 대한 트릭을 수행해야합니다.
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.Nums)을 사용하여 조금 단순화 할 수 있습니다.
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
);
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
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)
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)
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
for SQL Server 2017
declare @hits int = 0;
set @hits = (select count(*) from (select value from STRING_SPLIT('F609,4DFA,8499',',')) a);
select @hits;
You can use the following stored procedure to fetch , values.
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
The Replace/Len test is cute, but probably very inefficient (especially in terms of memory). A simple function with a loop will do the job.
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
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.
'Programing' 카테고리의 다른 글
| jQuery를 사용하여 (0) | 2020.06.23 |
|---|---|
| "|"는 무엇을 하는가 (0) | 2020.06.23 |
| Pythons timeit로 성능을 테스트하기 위해 코드 세그먼트의 시간을 어떻게 정할 수 있습니까? (0) | 2020.06.23 |
| DateTimePicker : 날짜와 시간을 모두 선택 (0) | 2020.06.23 |
| iOS 5.1 SDK의 iPad 시뮬레이터에 "홈"버튼이없는 이유는 무엇입니까? (0) | 2020.06.23 |