count the number of spaces in values in sql server [duplicate]

Question

I need the number of spaces in column values in sql server.

Ex:

column1
------------
aaa bbbb   - 1 space
aaa bbb ccc - 2 space
aaa bbb ccc ddd - 3 space

I need the count of spaces like this.

thanks.

Answer 1

SELECT LEN(column1)-LEN(REPLACE(column1, ' ', '')) FROM YourTableName

Answer 2

This will give a different and more accurate result than the other answers, it is also counting spaces in the end of the words, it becomes clear when tested on these examples:

DECLARE @a table(column1 varchar(20))
INSERT @a values('b c ')
INSERT @a values('b c')
INSERT @a values('   b c      ')

SELECT 
LEN(column1 + ';')-LEN(REPLACE(column1,' ','')) - 1 accurate,
LEN(column1)-LEN(REPLACE(column1,' ', '')) [inaccurate] -- other answers
FROM @a

Result:

accurate    inaccurate
2           1
1           1
10          4

Answer 3

Try this one -

DECLARE @t TABLE (txt VARCHAR(50))
INSERT INTO @t (txt)
VALUES 
      ('aaa bbbb')
    , ('aaa bbb ccc')
    , ('aaa bbb ccc ddd')

SELECT txt, LEN(txt) - LEN(REPLACE(txt, ' ', ''))
FROM @t