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
FutureQueryArtist
FutureQueryArtist
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 21
How do I write a query that tells me how many spaces (...or any character for that matter) are in a particular string?

Example:

David H Rogers would return a 2 since there are 2 spaces.
ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8911 Visits: 5555
This?

DECLARE  @String VARCHAR(100)
,@CharToFind VARCHAR(1)

SET @String = 'AAAA BBBCB NNNNN NEEEEE ERERERERERE '
SET @CharToFind = ' '

SELECT CountOfCharsInTheString = DATALENGTH (@String) - DATALENGTH(REPLACE(@String,@CharToFind,''))


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96423 Visits: 38981
Or this.



DECLARE @TestStr VARCHAR(20),
@CharToFind CHAR(1);
SET @TestStr = 'This is a string';
SET @CharToFind = ' ';

WITH
e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e4)
SELECT
COUNT(SUBSTRING(@TestStr, n, 1))
FROM
tally
WHERE
SUBSTRING(@TestStr, n, 1) = @CharToFind
AND n <= DATALENGTH(@TestStr);




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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218301 Visits: 41995
Hmmm.... I wonder which is 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
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9198 Visits: 8492
Shall we take a look at performance?

SET NOCOUNT ON;

IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
REPLACE(CAST(NEWID() AS VARCHAR(36)),'-',' ') AS string
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);

DECLARE @findMe CHAR(1) = ' ', @HOLDER INT;

PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);

PRINT '========== DATALENGTH - DATALENGTH REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = DATALENGTH (string) - DATALENGTH(REPLACE(string,@findMe,''))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);

PRINT '========== TALLY ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e4)
SELECT @HOLDER = COUNT(SUBSTRING(string, n, 1))
FROM tally
CROSS APPLY #testEnvironment
WHERE SUBSTRING(string, n, 1) = @findMe
AND n <= DATALENGTH(string);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);



Results:
========== BASELINE ==========
Table '#testEnvironment'. Scan count 5, logical reads 6818, physical reads 0, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 36 ms.
================================================================================
========== DATALENGTH - DATALENGTH REPLACE ==========
Table '#testEnvironment'. Scan count 1, logical reads 6818, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5641 ms, elapsed time = 5647 ms.
================================================================================
========== TALLY ==========
Table 'Worktable'. Scan count 10000, logical reads 3283222, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#testEnvironment'. Scan count 1, logical reads 6818, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 22890 ms, elapsed time = 8734 ms.
================================================================================


Of course, if we're going to look at performance, perhaps we should remember the bug with replace.

With that in mind, I propose changing ColdCoffee's solution to this: -
DECLARE @String VARCHAR(100), @CharToFind VARCHAR(1);

SET @String = 'AAAA BBBCB NNNNN NEEEEE ERERERERERE ';
SET @CharToFind = ' '

SELECT CountOfCharsInTheString = DATALENGTH(@String) - DATALENGTH(REPLACE(@String COLLATE Latin1_General_BIN2, @CharToFind, ''));



Let's check out the performance gain.
SET NOCOUNT ON;

IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
REPLACE(CAST(NEWID() AS VARCHAR(36)),'-',' ') AS string
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);

DECLARE @findMe CHAR(1) = ' ', @HOLDER INT;

PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);

PRINT '========== DATALENGTH - DATALENGTH REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = DATALENGTH (string) - DATALENGTH(REPLACE(string,@findMe,''))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);

PRINT '========== DATALENGTH - DATALENGTH REPLACE COLLATE Latin1_General_BIN2 ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = DATALENGTH (string) - DATALENGTH(REPLACE(string COLLATE Latin1_General_BIN2,@findMe,''))
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);



========== BASELINE ==========
Table '#testEnvironment'. Scan count 5, logical reads 6818, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 35 ms.
================================================================================
========== DATALENGTH - DATALENGTH REPLACE ==========
Table '#testEnvironment'. Scan count 1, logical reads 6818, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5594 ms, elapsed time = 5643 ms.
================================================================================
========== DATALENGTH - DATALENGTH REPLACE COLLATE Latin1_General_BIN2 ==========
Table '#testEnvironment'. Scan count 1, logical reads 6818, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 922 ms, elapsed time = 922 ms.
================================================================================


The huge difference remains no matter what order you execute or how many times you run it.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18111 Visits: 6431
Nice one Cadavre!

That's the same collation issue that I was trying to address the other day when timing REPLACE on another thread.

Just one question though. Did Jeff Moden put you up to this? He's been telling me that he was hoping other people would take up for him and run this kind of performance testing instead of him always having to do it. :-)

I like your test harnesses.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9198 Visits: 8492
dwain.c (6/20/2012)
Nice one Cadavre!

That's the same collation issue that I was trying to address the other day when timing REPLACE on another thread.

Just one question though. Did Jeff Moden put you up to this? He's been telling me that he was hoping other people would take up for him and run this kind of performance testing instead of him always having to do it. :-)

I like your test harnesses.


He didn't put me up to it, no. But I started my career at the same time as I signed up to this site (within a couple of months), so I picked up a couple of good habits from him and a lot of others along the way.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18111 Visits: 6431
Cadavre (6/20/2012)
dwain.c (6/20/2012)
Nice one Cadavre!

That's the same collation issue that I was trying to address the other day when timing REPLACE on another thread.

Just one question though. Did Jeff Moden put you up to this? He's been telling me that he was hoping other people would take up for him and run this kind of performance testing instead of him always having to do it. :-)

I like your test harnesses.


He didn't put me up to it, no. But I started my career at the same time as I signed up to this site (within a couple of months), so I picked up a couple of good habits from him and a lot of others along the way.


I'm finding I'm picking up lots of good habits from this site too. :-)

And dissing a few bad ones.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9198 Visits: 8492
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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
vinu512
vinu512
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3705 Visits: 1626
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. :-)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
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