Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

tsql query - Count the number of spaces in a string Expand / Collapse
Author
Message
Posted Wednesday, June 20, 2012 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 7,100, Visits: 6,927
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



Far away is close at hand in the images of elsewhere.

Anon.

Post #1318677
Posted Wednesday, June 20, 2012 11:37 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 3:17 PM
Points: 5, Visits: 21
I'll need to study this one. Wow, thanks for your effort and time.
Post #1318899
Posted Wednesday, June 20, 2012 6:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
Cadavre (6/20/2012)
Shall we take a look at performance?

Out-freakin'-standing, Craig! Well done and spot on especially with bringing up the collation problem. If no one rose to the occasion for testing, I was going to but, like Dwain said, I've been trying to get someone else besides me to do the testing. Since you've done this so often, I could have guessed it would be you to take up the ol' "A Developer must not guess, a Developer must KNOW" banner. Very well done and, yeah, it brings a tear of happiness to this ol' man's eyes to see someone do it so well. My hat's off to you.

I guess I'm going to have to ask the "which is faster" question more often instead of always doing it myself. Thank you VERY much for the effort you put into the test.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1319034
Posted Wednesday, June 20, 2012 7:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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


Oh my... you folks are way too kind . I'm just passing what I can forward. Thank you for the kind thoughts. If it weren't for this forum, I'd likely be in the dark ages myself.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1319040
Posted Wednesday, June 20, 2012 7:43 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 4:36 PM
Points: 494, Visits: 1,122
That is why Jeff is SQL God


+10



Looking for a Deadlock Victim Support Group..
Post #1319047
Posted Friday, April 19, 2013 4:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:44 AM
Points: 823, Visits: 107
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




Post #1444632
Posted Friday, April 19, 2013 4:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1444635
Posted Friday, April 19, 2013 8:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 23,302, Visits: 32,056
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444668
Posted Saturday, April 20, 2013 5:51 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 8,743, Visits: 9,292
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
Post #1444767
Posted Saturday, April 20, 2013 5:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:40 PM
Points: 8,743, Visits: 9,292
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
Post #1444768
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse