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, 
  1. select datalength('James 'as Length   
  2. 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,

  1. select DATALENGTH('James Anderson'as length    
  2. 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.


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


Now, I can easily conclude whether there is space inthe string or not by the results .
  1. if(LEN(RTRIM(LTRIM(@Data))) - LEN(REPLACE(@Data, ' '''))>0)    
  2. begin    
  3.     -- do something    
  4. end