Prev: SSRS 2005 questions
Next: how to find last character is int
From: Deep on 20 Dec 2009 12:58
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
From: Uri Dimant on 21 Dec 2009 01:32
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" <vinodkus(a)gmail.com> wrote in message
news:df3a866e-a10f-4f7a-b274-df2639730096(a)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


From: Deep on 21 Dec 2009 05:04
On Dec 21, 11:32 am, "Uri Dimant" <u...(a)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...(a)gmail.com> wrote in message
>
> news:df3a866e-a10f-4f7a-b274-df2639730096(a)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
 | 
Pages: 1
Prev: SSRS 2005 questions
Next: how to find last character is int