Post reply

Character Count from a Given String

  • June 5, 2009 at 2:39 am

    #134780

    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

  • June 5, 2009 at 2:45 am

    #1005081

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

    Does it perform better?

    -- Gianluca Sartori

  • June 5, 2009 at 2:55 am

    #1005085

    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

  • June 5, 2009 at 3:16 am

    #1005094

    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 🙂

  • June 5, 2009 at 3:18 am

    #1005095

    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

  • June 5, 2009 at 3:25 am

    #1005101

    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://qa.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

  • June 5, 2009 at 3:49 am

    #1005105

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

    Cheers

    🙂

  • June 5, 2009 at 4:04 am

    #1005108

    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"

  • June 5, 2009 at 1:49 pm

    #1005514

    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.

  • June 5, 2009 at 8:59 pm

    #1005618

    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)
    Intro to Tally Tables and Functions

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

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