count the number of spaces in values in sql server

<>

This question already has an answer here:

Answers


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

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

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

this is a code for that

select len('aaa bbb') - len(replace('aaa bbb ccc', ' ', '')) from 
**tablename**

output

1

select len('aaa bbb ccc') - len(replace('aaa bbb ccc', ' ', '')) from 
**tablename**

ouput

2

Tablename acan be anything table that can be in your database


Need Your Help

UnicodeEncodeError - works in Spyder but not when executed from terminal

python string unicode beautifulsoup codec

I'm using BeautifulSoup to Parse some html, with Spyder as my editor (both brilliant tools by the way!). The code runs fine in Spyder, but when I try to execute the .py file from terminal, I get an...

Automatic scroll a div down and back to top

javascript jquery html css

I've got a DIV, automaticly filled via PHP with Data. I need a small script that scrolls down inside the filled DIV if the DIV-size goes out of view (or reaches a maximum size) and then back to top,