11

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.

E.S
  • 536
  • 1
  • 9
  • 20
soundarrajan
  • 149
  • 1
  • 3
  • 13

4 Answers4

37
SELECT LEN(column1)-LEN(REPLACE(column1, ' ', '')) FROM YourTableName
Samuel Nde
  • 2,565
  • 2
  • 23
  • 23
Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83
12

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
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
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
Devart
  • 119,203
  • 23
  • 166
  • 186
  • 3
    @Devart your answer doesn't return the correct count when there are spaces in the end of the text. It will ignore the last spaces when counting. – t-clausen.dk Jan 30 '14 at 12:35
2

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

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
pankeel
  • 1,138
  • 1
  • 10
  • 22