CODEWITHSUNDEEP
sqlstringsearch
NImesh
NImesh

Reputation:

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

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'

Upvotes: 30

Views: 73552

Answers (4)

Odin Bujeiro
Odin Bujeiro

Reputation: 1

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

Upvotes: 0

Tiggyboo
Tiggyboo

Reputation: 562

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

Upvotes: 2

Rob Farley
Rob Farley

Reputation: 15849

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.

Upvotes: 14

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

Here you go:

declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
SELECT LEN(@string) - LEN(REPLACE(@string, 'd', '')) AS D_Count

Upvotes: 66

Related Questions