Developping for the translation industry RSS 2.0

 Thursday, July 29, 2010

Here is a simple way to get a specific character count (for example, count the number of occurences of the character ‘0’) and the word count of a varchar string using T-SQL.

-- Count the number of specific characters, in this case, ‘0’

Declare @value varchar(100)

Set @value = 'SQL Server 2000, SQL Server 2005, SQL Server 2008'

Select Len(@value) - Len(Replace(@value, '0', ''))


-- Count the number of words


SELECT @String = 'SQL Server 2005 Stan test code'

SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1


As you see this is quite straightforward, the script simply substract the length of the string minus the character searched from the full length of the string, giving as a result the character count. The script counting words is simply counting the space characters.


Other posts:

How to remove multiple whitespaces from a string with SQL Server 2005

How to generate random numbers with a SQL query

How to remove leading zeros from the results of an SQL Query

How to Find The List Of Unused Tables Since The Last SQL Server Restart

Which performs better: ISNULL or COALESCE

Thursday, July 29, 2010 8:58:32 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL

About the author/Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2019
Stanislas Biron
Sign In
Total Posts: 135
This Year: 0
This Month: 0
This Week: 0
Comments: 1
All Content © 2019, Stanislas Biron