SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Character Count from a Given String

Character Count from a Given String Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
CrazyMan
Posted Friday, June 05, 2009 2:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 03, 2010 4:13 AM
Points: 719, Visits: 1,421
Hi All
I got in a situation where i have to clean my table which is backs to an year worth of data, My problem is that i have a Varchar column in the table, i need to find the length of the varchar column, i cant use Len ( Column1),

The problem is that i have to count some characters as Length 2 instead of 1, say for example if i find character 'A' on the string then its considered as length 2.

I wrote a function that loops through the string and checks each character by character. This is time and resource consuming.

Is there any better way of doing this on 2005 or on 2008, the examples are as below

Declare @Text1 Varchar(max)
Set @Text1 ='Test Message'

Condition is that 'E' and 'A' must be counted as 2

If i do select len(text1) i will get a result 12, but i the result i should get is 15 ( that is 12 +2+1 (2 instances of ''E' and '1' instance of a))

Can you please help me on this

Thanks in advance for your help

Cheers
Post #729480
Gianluca Sartori
Posted Friday, June 05, 2009 2:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 1,280, Visits: 1,798
Did you try LEN(REPLACE(@charstring, 'A', 'AA') ?
Does it perform better?
Post #729485
Mark-101232
Posted Friday, June 05, 2009 2:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 593, Visits: 8,245
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)

____________________________________________________

How to get the best help on a forum

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

There are 10 types of people in the world — those who understand binary, and those who don't.
Post #729489
CrazyMan
Posted Friday, June 05, 2009 3:16 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 03, 2010 4:13 AM
Points: 719, Visits: 1,421
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
Post #729498
CrazyMan
Posted Friday, June 05, 2009 3:18 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 03, 2010 4:13 AM
Points: 719, Visits: 1,421
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
Post #729500
Mark-101232
Posted Friday, June 05, 2009 3:25 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 593, Visits: 8,245
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/

____________________________________________________

How to get the best help on a forum

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

There are 10 types of people in the world — those who understand binary, and those who don't.
Post #729507
CrazyMan
Posted Friday, June 05, 2009 3:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 03, 2010 4:13 AM
Points: 719, Visits: 1,421
Thanks Mark, this is an interesting new dimension, Looks great, i will work on this and let you know my result

Cheers
Post #729515
Kruti Kansara
Posted Friday, June 05, 2009 4:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 6:52 AM
Points: 468, Visits: 392
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"
Post #729523
Aaron-237544
Posted Friday, June 05, 2009 1:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 11, 2009 2:56 PM
Points: 19, Visits: 103
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.
Post #730012
Jeff Moden
Posted Friday, June 05, 2009 8:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 19,574, Visits: 13,213
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."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #730134
« Prev Topic | Next Topic »


Permissions Expand / Collapse

 
 
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use