Number of times a particular character appears in a string

by

There’s no direct function for this, but you can do it with a replace:

declare @myvar varchar(20)
set @myvar="Hello World"

select len(@myvar) - len(replace(@myvar,'o',''))

Basically this tells you how many chars were removed, and therefore how many instances of it there were.

Extra:

The above can be extended to count the occurences of a multi-char string by dividing by the length of the string being searched for. For example:

declare @myvar varchar(max), @tocount varchar(20)
set @myvar="Hello World, Hello World"
set @tocount="lo"

select (len(@myvar) - len(replace(@myvar,@tocount,''))) / LEN(@tocount)

More Related Contents:

  1. incorrect syntax near keyword where [closed]
  2. nvarchar concatenation / index / nvarchar(max) inexplicable behavior
  3. How do I create a unique constraint that also allows nulls?
  4. How do I get list of all tables in a database using TSQL?
  5. How to convert SQL Server’s timestamp column to datetime format
  6. Escape a string in SQL Server so that it is safe to use in LIKE expression
  7. Storing files in SQL Server
  8. Dapper.NET and stored proc with multiple result sets
  9. Import ‘xml’ into Sql Server
  10. Query times out when executed from web, but super-fast when executed from SSMS
  11. T-SQL Dynamic SQL and Temp Tables
  12. Efficient ISNUMERIC() replacements on SQL Server?
  13. Login to Microsoft SQL Server Error: 18456
  14. Adding a query hint when calling Table-Valued Function
  15. What is a simple command line program or script to backup SQL server databases?
  16. Disadvantages of MARS (Multiple Active Result Sets)?
  17. SQL Server Output Clause into a scalar variable
  18. Replace single quotes in SQL Server
  19. Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?
  20. sql use statement with variable
  21. How to create a yes/no boolean field in SQL server?
  22. How do I select last 5 rows in a table without sorting?
  23. displaying multiple records by using resultset
  24. How to display two digits after decimal point in SQL Server
  25. Copy table to a different database on a different SQL Server
  26. Include missing months in Group By query
  27. How to change column datatype in SQL Server database without losing data?
  28. Convert UTC Milliseconds to DATETIME in SQL server
  29. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  30. Tool for Scripting Table Data

Leave a Comment