Wednesday, March 3, 2010

To get the number of occurrence of a character in SQL

Wonder how to get the number occurrence of a character in MS SQL?
Here's a trick on how to do it

declare @char varchar(1), @str varchar(100)
SET @char ='/'
SET @str = '/102///111/'
SELECT LEN(@str)-LEN(REPLACE(@str,@char,''))

Wala.. there u go, u will get 5 for the scenario above.

No comments: