Count the number of spaces in a string

  • A+
Category:Languages

I am working on data validation and I am trying to count the number of spaces in a string. My problem is that when I count the spaces, any sting with more than one space between texts or any string with trailing space(s) are not counted I have tried the following codes without luck. each codes gives different result but not the desired output

DECLARE @MyTbl TABLE (ID INT, Name VARCHAR(300)) INSERT INTO @MyTBL VALUES (1, 'Alfreds Futterkiste'), -- 1 space (2,'Mike James Ray  '),     -- 4 spaces 1 space between each text and 2 spaces after text (3,'Hanari  Carnes'),       -- 2 spaces between text (4,'James Michael')  -- 1 SELECT ID, LEN(Name)-LEN(REPLACE(Name, ' ', '')) AS Count_Of_Spaces  FROM  @MyTBL -- 2 SELECT ID, LEN(Name + ';')-LEN(REPLACE(Name,' ','')) AS Count_Of_Spaces2  FROM  @MyTBL -- 3 SELECT ID, LEN(Name)-LEN(REPLACE(Name,' ', '')) AS Count_Of_Spaces3   FROM  @MyTBL 

Current output based on the first query

 ID Count_Of_Spaces   1    1   2    2   3    2   4    1 

Desired output

 ID Count_Of_Spaces  1     1  2     4  3     2  4     1 


You could use DATALENGTH:

SELECT ID, DATALENGTH(Name)-LEN(REPLACE(Name,' ', '')) AS Count_Of_Spaces FROM  @MyTBL; 

DBFiddle Demo

LEN does not count trailing spaces.


If NVARCHAR then you need to divide by 2.

DECLARE @MyTbl TABLE (ID INT, Name NVARCHAR(300)) INSERT INTO @MyTBL VALUES (1, 'Alfreds Futterkiste'), -- 1 space (2,'Mike James Ray  '),     -- 4 spaces 1 space between                              -- each text and 2 spaces after text (3,'Hanari  Carnes'),       -- 2 spaces between text (4,'James Michael');  SELECT ID, DATALENGTH(Name)/2-LEN(REPLACE(Name,' ', '')) AS Count_Of_Spaces FROM  @MyTBL; 

DBFiddle Demo2

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: