SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


tsql query - Count the number of spaces in a string


tsql query - Count the number of spaces in a string

Author
Message
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9548 Visits: 9751
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 :-D

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.


FutureQueryArtist
FutureQueryArtist
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 21
I'll need to study this one. Wow, thanks for your effort and time.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85741 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85741 Visits: 41091
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 :-D

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 Blush. 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Possinator
Possinator
Mr or Mrs. 500
Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)Mr or Mrs. 500 (590 reputation)

Group: General Forum Members
Points: 590 Visits: 1123
That is why Jeff is SQL God


+10

Looking for a Deadlock Victim Support Group..
Ed Thompson
Ed Thompson
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 122
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85741 Visits: 41091
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39239 Visits: 38529
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




Cool
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)
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14270 Visits: 12197
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

TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14270 Visits: 12197
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 :-D

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search