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

  • A+
Category:Languages

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.


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.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: