April 19, 2013 at 4:19 pm
Very nice. I needed some code to find the number of occurrences of more than one character and I found that all I had to do was add a divisor.
CREATE FUNCTION [dbo].[fn_NumOccurrences]
(
@sourceString varchar(1000)
, @searchString varchar(10)
)
RETURNS INTEGER
AS
BEGIN
DECLARE @numTimes INTEGER = 0
SELECT @numTimes = (DATALENGTH(@sourceString) - DATALENGTH(REPLACE(@sourceString COLLATE Latin1_General_BIN2, @searchString, ''))) / DATALENGTH(@searchString);
RETURN @numTimes
END
April 19, 2013 at 4:31 pm
Ed Thompson (4/19/2013)
Very nice. I needed some code to find the number of occurrences of more than one character and I found that all I had to do was add a divisor.CREATE FUNCTION [dbo].[fn_NumOccurrences]
(
@sourceString varchar(1000)
, @searchString varchar(10)
)
RETURNS INTEGER
AS
BEGIN
DECLARE @numTimes INTEGER = 0
SELECT @numTimes = (DATALENGTH(@sourceString) - DATALENGTH(REPLACE(@sourceString COLLATE Latin1_General_BIN2, @searchString, ''))) / DATALENGTH(@searchString);
RETURN @numTimes
END
If you were to turn that into a single element Inline Table Valued Function (iTVF), it would be even faster.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 19, 2013 at 8:53 pm
Like this:
CREATE FUNCTION [dbo].[ifn_NumOccurrences]
(
@sourceString varchar(1000),
@searchString varchar(10)
)
RETURNS TABLE
AS
return
SELECT numTimes = (DATALENGTH(@sourceString) - DATALENGTH(REPLACE(@sourceString COLLATE Latin1_General_BIN2, @searchString, ''))) / DATALENGTH(@searchString);
GO
April 20, 2013 at 5:51 pm
Lynn Pettis (4/19/2013)
Like this:
CREATE FUNCTION [dbo].[ifn_NumOccurrences]
(
@sourceString varchar(1000),
@searchString varchar(10)
)
RETURNS TABLE
AS
return
SELECT numTimes = (DATALENGTH(@sourceString) - DATALENGTH(REPLACE(@sourceString COLLATE Latin1_General_BIN2, @searchString, ''))) / DATALENGTH(@searchString);
GO
But people have to be aware of the problem that if the search string ends with one or more spaces but also has other characters, this will sometimes return a result too low by 1 (whenever the final ocurrence of the search string in the sourcestring is followed only by spaces), whether or not it's done as an iTVF.
Tom
April 20, 2013 at 5:59 pm
David Burrows (6/20/2012)
vinu512 (6/20/2012)
Cadavre (6/20/2012)
Well, I doubt I'd be the developer I am today (or have the job I currently have) without Jeff and a lot of other people from this site (way too many to mention and I'd be bound to miss someone).This forum is a god-send, because it introduces you to so many extremely talented people that can in turn introduce you to new ideas.
I completely agree with you Cadavre.
I've just started my career in SQL Server. Joined this Forum and made my first post a month after I started working on SQL server.
I am that young when it comes to Sql Server and am still learning.
But, I must say that I have learnt a lot of(ie: a whole lot of) things from SSC posts and solutions provided by the very learned Gurus of SSC. 🙂
That is why Jeff is SQL God 😀
p.s. I too learn/learned a lot from Jeff, bet this brings tear to his eye 😉
It's not just you youngsters who learn from Jeff, even grey haired ancients like me have learned a lot from him. I think I first looked at SQLC because a search turned up an article by Jeff, but I'm not sure - but I am absolutely sure that it was reading Jeff's stuff that caused me to stay around here; and of course staying here I've discovered rather a lot more people I can learn from.
Tom
April 21, 2013 at 5:53 pm
L' Eomot Inversé (4/20/2013)
David Burrows (6/20/2012)
vinu512 (6/20/2012)
Cadavre (6/20/2012)
Well, I doubt I'd be the developer I am today (or have the job I currently have) without Jeff and a lot of other people from this site (way too many to mention and I'd be bound to miss someone).This forum is a god-send, because it introduces you to so many extremely talented people that can in turn introduce you to new ideas.
I completely agree with you Cadavre.
I've just started my career in SQL Server. Joined this Forum and made my first post a month after I started working on SQL server.
I am that young when it comes to Sql Server and am still learning.
But, I must say that I have learnt a lot of(ie: a whole lot of) things from SSC posts and solutions provided by the very learned Gurus of SSC. 🙂
That is why Jeff is SQL God 😀
p.s. I too learn/learned a lot from Jeff, bet this brings tear to his eye 😉
It's not just you youngsters who learn from Jeff, even grey haired ancients like me have learned a lot from him. I think I first looked at SQLC because a search turned up an article by Jeff, but I'm not sure - but I am absolutely sure that it was reading Jeff's stuff that caused me to stay around here; and of course staying here I've discovered rather a lot more people I can learn from.
Definitely a +1 to that!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 21, 2013 at 9:03 pm
You good folks are too kind. :blush: I'm just trying to help because I didn't have any help when I started and know what that's like. Thank all of you for what you've done on these fine forums. We make one hell of a team.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply