Menu
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.
894
One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.
SQL Server LEN() Function The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.
SELECT LEN(column1)-LEN(REPLACE(column1, ' ', '')) FROM YourTableName
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
