Find Number of Character Repeated

Question

Post reply

Find Number of Character Repeated

karthik M

One Orange Chip

Points: 29139
More actions
December 21, 2007 at 3:58 am

#177655

Hi,
I want to find out the number of characters repeated in the given string.
For example,
Declare @STR varchar(25)
set @STR = 'SQLSERVERCENTRAL'
Output :
Letters No.Of.Time
S 2
E 3
R 2
L 2
Inputs are welcome.
karthik

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

karthikeyan,

Now that was a fun little exercise! Below is a function that will return a table.

CREATE FUNCTION [dbo].[fnCountCharacterOccurrence]

(@String NVARCHAR(4000))

RETURNS @Results TABLE (item NCHAR(1), itemCount INT)

AS

BEGIN

SET @String = 'SQLSERVERCENTRAL'

INSERT @Results

SELECT

item, COUNT(*)

FROM

(SELECT

SUBSTRING(@String,Number,1) AS item, 0 AS y

FROM

Master.dbo.spt_Values

WHERE

[Type] = 'P'

AND Number BETWEEN 1 AND LEN(@String)) x

GROUP BY

item

RETURN

END

GO

-- now test

SELECT

*

FROM

dbo.fnCountCharacterOccurrence('SQLSERVERCENTRAL')

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

karthik M One Orange Chip Points: 29139 More actions · Answer 2

I don't have the permission to create function.Can you give me the solution without function ?

karthik

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

Sure,

DECLARE @String NVARCHAR(4000)

SET @String = 'SQLSERVERCENTRAL'

SELECT

item, COUNT(*)

FROM

(SELECT

-- this will get the specific letter for each position in the string

SUBSTRING(@String,Number,1) AS item

,0 AS y -- just a placeholder

FROM

Master.dbo.spt_Values

WHERE

[Type] = 'P'

AND Number BETWEEN 1 AND LEN(@String)) x

GROUP BY

item

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Can you tell me where exactly you can apply spt_values table?

I mean In which situations you go to spt_values.can you list out some examples ?

Also, say for example if i have more than one word in the string variable,

for example,

Declare @STR varchar(100)

Set @STR = 'WELCOME,SQLSERVERCENTRAL,LIRIL,CELLPHONE'

Will our code work ? or do we need to change some part of the code ?

Expected Output:

String Name Letter No.Of.Time

WELCOME E 2

SQLSERVERCENTRAL S 2

E 3

R 2

L 2

LIRIL L 2

I 2

CELLPHONE E 2

L 2

Thanks in Advance.

karthik

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

karthikeyan (12/21/2007)
Can you tell me where exactly you can apply spt_values table?
I mean In which situations you go to spt_values.can you list out some examples ?

I thought Mr. Moden had done this already. I learned by ready his posts/solutions. Try looking at his posts and you get a ton of examples.

Also, say for example if i have more than one word in the string variable,
for example,
Declare @STR varchar(100)
Set @STR = 'WELCOME,SQLSERVERCENTRAL,LIRIL,CELLPHONE'
Will our code work ? or do we need to change some part of the code ?

With all respect, why don't you run it with multiple words and see for yourself. 😀

But, No, my code was written to handle one word. It will count the number of times a character appears in the entire string.

[/quote]

hope this helps...

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

karthik M One Orange Chip Points: 29139 More actions · Answer 6

karthik M

One Orange Chip

Points: 29139

December 21, 2007 at 5:19 am

#762066

I am going to try it and get back to you.

karthik

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

Jason Selburg (12/21/2007)
karthikeyan (12/21/2007)
Can you tell me where exactly you can apply spt_values table?
I mean In which situations you go to spt_values.can you list out some examples ?
I thought Mr. Moden had done this already. I learned by ready his posts/solutions. Try looking at his posts and you get a ton of examples.

I did, indeed... but to emphasize what I've already said, the best thing to do is to NOT use spt_values at all... use a Tally table instead. spt_values only has a limited number of, well, numbers and even in 2k5, the numbers are not sufficient to traverse the full width of a VARCHAR(8000).

The only reason why folks like Jason, Madhivinan, and I use it in our examples in this forum is that it's a lot more convenient than telling folks how to build a Tally table over and over and over and... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

karthikeyan (12/21/2007)
Can you tell me where exactly you can apply spt_values table?
I mean In which situations you go to spt_values.can you list out some examples ?
Also, say for example if i have more than one word in the string variable,
for example,
Declare @STR varchar(100)
Set @STR = 'WELCOME,SQLSERVERCENTRAL,LIRIL,CELLPHONE'
Will our code work ? or do we need to change some part of the code ?
Expected Output:
String Name Letter No.Of.Time
WELCOME E 2
SQLSERVERCENTRAL S 2
E 3
R 2
L 2
LIRIL L 2
I 2
CELLPHONE E 2
L 2
Thanks in Advance.

String manipulations can be done easily with number tables without using while loop or cursor. If you deal with small set of data then instead of creating a new number table you can make use of master..spt_values table

Run this and see the result

select number from master..spt_values

where type='p'

order by number

Here are some examples that I made use of master..spt_values table fro string manipulation

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/21/count-number-of-words-in-a-string.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail