29

How can you find the number of occurrences of a particular character in a string using sql?

Example: I want to find the number of times the letter ‘d’ appears in this string.

declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
xsl
  • 17,116
  • 18
  • 71
  • 112

4 Answers4

64

Here you go:

declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
SELECT LEN(@string) - LEN(REPLACE(@string, 'd', '')) AS D_Count
Mladen Prajdic
  • 15,457
  • 2
  • 43
  • 51
  • Cute, I was thinking of iterating with charindex, I like this much better. – vfilby Nov 13 '08 at 16:22
  • I agree with vfilby and can confirm this code works in SQL Server 2005 and 2008. – xsl Nov 13 '08 at 16:24
  • I am a MySql user. and definately not a guru. What would be the equiv of D_Count in MySQL? – J.J. Nov 13 '08 at 16:38
  • D_Count is just an alias for the column. i don't know how you set that in MySql – Mladen Prajdic Nov 13 '08 at 16:42
  • 1
    You should also divide by the length of what you're looking for. This script works because 'd' is only one character long. If you were looking for 'as', you'd have to divide the answer by two. – Rob Farley Mar 11 '10 at 01:27
  • Sybase ASE (15.7 at least) requires str_replace instead of replace, and null instead of '' for the replacement character. – Tiggyboo Mar 28 '18 at 19:44
14

If you want to make it a little more general, you should divide by the length of the thing you're looking for. Like this:

declare @searchstring varchar(10);
set @searchstring = 'Rob';

select original_string, 
(len(orginal_string) - len(replace(original_string, @searchstring, '')) 
   / len(@searchstring)
from someTable;

This is because each time you find 'Rob', you remove three characters. So when you remove six characters, you've found 'Rob' twice.

Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • This figures you're querying a table called `someTable` which has a column called `original_string`. And the principle will work in any database, you just need to find the equivalent functions. – Rob Farley Mar 11 '10 at 01:34
2

For all you Sybase ASE 15 dinosaurs our there, you will need to replace '' with null, i.e.

SELECT LEN(@string) - LEN(REPLACE(@string, 'd', null)) AS D_Count
Tiggyboo
  • 522
  • 6
  • 14
0

In SQl Server if @string = 'sfdasa ddd' function LEN fails to count trailing white spaces. Solution to LEN's issue is in LEN function not including trailing spaces in SQL Server

Odin Bujeiro
  • 1
  • 2
  • While interesting, this does not actually answer the question. Perhaps it could be a comment for another answer. – ttarchala Sep 02 '22 at 18:32