Get the Space Count of String in SQL

In this blog you will learn how to get the space count of string in SQL.

Today I came across one scenario where I need to chec if the space in string exists or not. I have planned to work with the datalength  function which will give the length of the string with space and len function which gives the length of the string without including space in count.

 

For example,consider the below query, 

select datalength('James ') as Length
select len('James ') as Length

 Which returns the result as shown below,

 

  
Now, I can compare these two results and I can conclude whether there is space in data or not,

Just come to a below scenario,

select DATALENGTH('James Anderson') as length
select len('James Anderson') as length

Which returns,

 
Now, it's hard to conclude whether there is space in the string or not by comparing the two results .

To make it simple, we need to do some logical query to get the space count in string which can done using TRIM methods.

Query 

SELECT LEN(RTRIM(LTRIM('James Anderson')))-LEN(REPLACE('James Anderson',' ',''))  as Spacecount

Result: 

 
Now, I can easily conclude whether there is space inthe string or not by the results .

if(LEN(RTRIM(LTRIM(@Data))) - LEN(REPLACE(@Data, ' ', ''))>0)
begin
    -- do something
end