Hello Partners, somebody know if exists some function for counting the number of times that a character is inside of a string?
-
Hello Partners, somebody know if exists some function or something for counting the number of times that a character is inside of a string?
Thanks a lot of.............
-
I don't believe there is such a SQL Server function but you can try this:
CREATE FUNCTION [dbo].[CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN
DECLARE @vInputLength INT
DECLARE @vIndex INT
DECLARE @vCount INT
SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)
WHILE @vIndex <= @vInputLength
BEGIN
IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
SET @vCount = @vCount + 1
SET @vIndex = @vIndex + 1
END
RETURN @vCount
END
-
Thanks you are very kind ....
-
Here's something that'll work much faster :
Declare @String as varchar(150)
Declare @Search as varchar(10)
set @String = 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
set @Search = 'a'
Select (DATALENGTH(@String) - DATALENGTH(REPLACE(@String, @Search, ''))) / DATALENGTH(@Search) AS Occurences --division needed only if you replace strings vs only single chars
-
Thanks , you know i am thinking a question
what is the difference between use a function instead of a store procedure?
exists some diffrences of perfomance???
interesting....
-
Functions are executed on a row by row basis. So if you have 5k rows, sql server will have to execute the function 5k times. This can become a huge bottleneck if you have selects in the function. It would still be a bottleneck in this case but on a much smaller scale since there are only a few variables to set.
The other main advantage in this case is that only 3 fonction calls are made per line, vs 1 / char / line with the function which will provide better performance. We could also assume that any native sql function coded in C ?++ will run faster than anything we can come up with in t-sql.
-
Remi,
That was a good trick. This is one of the reasons why I join these forums. I always learn something new.
-
It's the same reason I'm always here... Learn something new everyday.
-
Nice one, Remi! Very sweet....can't bum that at all.
As an aside on functions. I've tested this, and found little to no performance degradation in putting code like this into functions, specifically when they are "deterministic" and in the same database as the tables in the query. With the addition on "With SCHEMABINDING", your function is deterministic:
create function UTIL_Instances(@String varchar(8000), @Search varchar(255))
returns int
with SCHEMABINDING
as
BEGIN
return
(
Select (DATALENGTH(@String) - DATALENGTH(REPLACE(@String, @Search, ''))) / DATALENGTH(@Search)
)
END
---shows object properties (1 is deterministic)
select OBJECTPROPERTY ( object_ID('UTIL_Instances') , 'IsDeterministic' )
I believe this is due to deterministic functions operating on all rows in the table at the same time, similar to what would happen if the code was in-line. Using functions is optimal, if they perform well, if even just for ease of use (reading, re-using...)
Signature is NULL
-
Join the both ideas
Thanks
-
Yup... maybe we should post it as a script??
-
Definitely...you should do that! I just wrapped it in the function syntax and added Schemabinding. Well worth posting, and will probably become a "script of the day". You may want to think of a better name, though.
cl
Signature is NULL
-
There's already a script posted that does exactly that, so I won't repost.
-
Oh, Well, good thing you checked, eh? Still a good piece of code, even if somebody else did it first.
Signature is NULL
-
I read this code on these forums... I definitly didn't invent anything here
.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply