Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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