T-SQL Query | [ Count Spaces in a string Puzzle ]

The puzzle is very simple. Here you have to count the spaces present in the string; there are multiple ways to achieve this, Please post all the possible solutions. Please check out the sample input and expected output for details.

Sample Input

 texts P Q R S L M N O 0 0 I  am here only Welcome to the new world Hello world program Are u nuts

Expected output

 Data SpacesPresent P Q R S 4 L M N O 0 0 11 I  am here only 5 Welcome to the new world 6 Hello world program 3 Are u nuts 4

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` CREATE TABLE CountSpaces ( texts VARCHAR(100) ) GO INSERT INTO CountSpaces(texts) VALUES ('P Q R S '), (' L M N O 0 0     '), ('I  am here only '), (' Welcome to the new world '), (' Hello world program'), (' Are u nuts ') ```

UPDATE – 11-Apr-2015 – Solution 1

 ``` -- --NOTE In case of VARCHAR --Solution 1 SELECT texts Data, DATALENGTH(texts) - DATALENGTH(REPLACE(texts,' ', '')) SpacesPresent FROM CountSpaces --Solution 2 SELECT texts Data, LEN(texts+'a') - LEN(REPLACE(texts,' ', '') + 'a') SpacesPresent FROM CountSpaces --NOTE In case of NVARCHAR --Solution 1 SELECT texts Data, LEN(texts+'a') - LEN(REPLACE(texts,' ', '') + 'a') SpacesPresent FROM CountSpaces --Solution 2 SELECT texts Data , DATALENGTH(CAST(texts AS VARCHAR(MAX))) - LEN(REPLACE(CAST(texts AS VARCHAR(MAX)),' ', '')) SpacesPresent FROM CountSpaces -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com