Character Count from a Given String

Question

Post reply

Character Count from a Given String

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Did you try LEN(REPLACE(@charstring, 'A', 'AA') ?

Does it perform better?

-- Gianluca Sartori

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 2

Using a numbers/tally table

--Pre-populate with characters of length > 1

DECLARE @Lengths TABLE(Ch CHAR(1) PRIMARY KEY, Length INT)

INSERT INTO @Lengths(Ch,Length)

SELECT 'A',2 UNION ALL

SELECT 'E',2

Declare @Text1 Varchar(max)

Set @Text1 ='Test Message'

SELECT SUM(COALESCE(l.Length,1))

FROM Numbers n

LEFT OUTER JOIN @Lengths l ON l.Ch=SUBSTRING(@Text1,n.Number,1)

WHERE n.Number BETWEEN 1 AND LEN(@Text1)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Thanks Guys

I cant really get the numbers table, can you please throw some light on the Numbers tables which is involved on the join 🙂

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Thanks mate, there are some ascii characters which i need to check them , its a good idea, but i need to implement this on the live services at some point

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 5

CrazyMan (6/5/2009)
Thanks Guys
I cant really get the numbers table, can you please throw some light on the Numbers tables which is involved on the join 🙂

Apologies, should have added a link to this

http://www.sqlservercentral.com/articles/T-SQL/62867/

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

CrazyMan SSChampion Points: 12560 More actions · Answer 6

Thanks Mark, this is an interesting new dimension, Looks great, i will work on this and let you know my result

Cheers

🙂

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 7

Will this work?

Declare @Text1 Varchar(max)

Set @Text1 ='Test Message'

select len(@text1)+(LEN(@text1) - LEN(REPLACE(@text1, 'e', '')))+(LEN(@text1) - LEN(REPLACE(@text1, 'a', '')))

"Don't limit your challenges, challenge your limits"

Aaron-237544 SSC Veteran Points: 265 More actions · Answer 8

This question completely depends on WHY you need to count A's and E's as double characters. Is English the native language for this database? Because if UNICODE characters are the reasons why you need to consider 2 bytes per character only for some characters, you may be going down the wrong path.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

As Aaron suggests, you might want to let us know why you need to do this so we can ensure you get the best solution.

In the mean time and as big a fan I am of the Tally or Numbers table, I believe that Gianluca Sartori's may be the quickest provided that the doubling of the key characters doesn't cause an overflow of the datatype used.

--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)