Count the number of spaces in a string

Question

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

Answer 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