Count spaces (ASCII code 32) at the beginning and end of string

Question

I'm struggling with the following problem. I have a column with this kind of data:

'abbb ccc   '
'    aaa abbb ccc'
'abbb ccc   '
'   aaa abbb ccc   '
'   ccc'
'aaa abbb'

I want to count the number of spaces on the left and the number of spaces on the right of each string.

Answer 1

Try using a combination of LEN, LTRIM, and REVERSE:

SELECT
    LEN(col) - LEN(LTRIM(col)) AS num_left,
    LEN(REVERSE(col)) - LEN(LTRIM(REVERSE(col))) AS num_right
FROM yourTable;

Demo

As @AaronDietz mentioned in the comment below, LEN actually also trims whitespace on the right. But LEN does not affect leading whitespace. To compensate for this we can reverse the string and then do the calculation using LTRIM.

Answer 2

DECLARE @s NVARCHAR(50)='abbb ccc   '
DECLARE @t NVARCHAR(50)='    aaa abbb ccc'
SELECT
    RightSpaces = LEN(RTRIM(REVERSE(@s))) - LEN(@s),
    LeftSpaces = LEN(@t) - LEN(LTRIM(REVERSE(@t)))

output

RightSpaces LeftSpaces
3           4

or you can use DATALENGTH and remove the need for REVERSE

SELECT
    LeftSpaces  = (DATALENGTH(@s)- DATALENGTH(RTRIM(@s)))/2,
    RightSpaces = (DATALENGTH(@t)- DATALENGTH(LTRIM(@t)))/2

output

RightSpaces LeftSpaces
3           4

Answer 3

You can ditch LEN and TRIM functions and use PATINDEX instead:

SELECT
    str,
    PATINDEX('%[^ ]%', str) - 1 AS leading_spaces,
    PATINDEX('%[^ ]%', REVERSE(str)) - 1 AS trailing_spaces
FROM testdata

Output:

| str                | leading_spaces | trailing_spaces |
+--------------------+----------------+-----------------+
| abbb·ccc···        | 0              | 3               |
| ····aaa·abbb·ccc   | 4              | 0               |
| abbb·ccc···        | 0              | 3               |
| ···aaa·abbb·ccc··· | 3              | 3               |
| ···ccc             | 3              | 0               |
| aaa·abbb           | 0              | 0               |

SQL Fiddle