Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

occurence of charcter no of times

0 views
Skip to first unread message

Deep

unread,
Dec 20, 2009, 12:58:10 PM12/20/09
to
Dear sir/madam

I have to find the no of occurence of character in a sting in sql
server. What any function is like that.
For eg

'my name is vinod kumar'

here 'i' letter is two times.

Can I know with any function.

Thanks in Advance

Vinod

Uri Dimant

unread,
Dec 21, 2009, 1:32:59 AM12/21/09
to
Deep
I have modified the original script writte by Peter Larsson
DECLARE @s VARCHAR(100)


SET @s = 'my name is vinod kumar'


-- Longest sequence of same character

;WITH cte

AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY [char] ORDER BY [char]) rn,

[char]

FROM (

SELECT SUBSTRING(@s, 1 + Number, 1) [char],

Number,

PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 +
Number, 8000)) AS [seq]

FROM master..spt_values

WHERE Number < DATALENGTH(@s)

AND type = 'P'

) AS q

) SELECT MAX(rn) FROM cte WHERE [char]='i'

"Deep" <vino...@gmail.com> wrote in message
news:df3a866e-a10f-4f7a...@k19g2000pro.googlegroups.com...

Deep

unread,
Dec 21, 2009, 5:04:48 AM12/21/09
to
On Dec 21, 11:32 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> Deep
> I have modified the original script writte by Peter Larsson
> DECLARE @s VARCHAR(100)
>
> SET @s = 'my name is vinod kumar'
>
> -- Longest sequence of same character
>
> ;WITH cte
>
> AS
>
> (
>
> SELECT ROW_NUMBER() OVER (PARTITION BY [char] ORDER BY [char]) rn,
>
> [char]
>
> FROM (
>
> SELECT SUBSTRING(@s, 1 + Number, 1) [char],
>
> Number,
>
> PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 +
> Number, 8000)) AS [seq]
>
> FROM master..spt_values
>
> WHERE Number < DATALENGTH(@s)
>
> AND type = 'P'
>
> ) AS q
>
> ) SELECT MAX(rn) FROM cte WHERE [char]='i'
>
> "Deep" <vinod...@gmail.com> wrote in message

>
> news:df3a866e-a10f-4f7a...@k19g2000pro.googlegroups.com...
>
>
>
> > Dear sir/madam
>
> > I have to find the no of occurence of character in a sting in sql
> > server. What any function is like that.
> > For eg
>
> > 'my name is vinod kumar'
>
> > here 'i' letter is two times.
>
> > Can I know with any function.
>
> > Thanks in Advance
>
> > Vinod- Hide quoted text -
>
> - Show quoted text -

Thanks

0 new messages