Archives of the TeradataForum

Message Posted: Thu, 04 Jan 2007 @ 10:07:26 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, January 03, 2007 20:59 -->

Maybe I'm a purist but I tend to treat strings as just more data to handle. Hence if you want to COUNT something in SQL you need to get it into multiple rows.

For performing various and unforeseen expansions I keep a table of numbers (filled from 1 up to however large I've needed so far).

Using that table I can expand the characters in the strings and then count them

e.g.

     SELECT
       ID ,
       COUNT(*)
     FROM
       ( SELECT
           StrSrc.ID ,
           SUBSTRING( StrSrc.TestStr FROM N FOR 1) AS CHAR_AT ,
           N
         FROM
           ( SELECT
               ID ,
               TestStr
             FROM
               A_Table_With_Keys_and_Strings
             ) AS StrSrc
         CROSS JOIN
           ( SELECT
               N ,
               'C' AS C_CHAR
             FROM
               Number_N
             WHERE
               N < 20
             ) AS N_C
         WHERE
           CHAR_AT = N_C.C_CHAR
         ) Xpanded
     GROUP BY
       ID
     ;

where

- Number_N is the list of numbers

- A_Table_With_Keys_and_Strings is a source of a string column with a key per row

There is a where clause (N<20) to limit the size of numbers used for the expansion.

I usually have some other row source of characters to count - for counting the occurrences of a single character I've just set a constant value with 'C' AS C_CHAR.

I find this approach very flexible and pretty much SQL dialect independent. The row blowout is usually made up for by the narrowness of columns.

	<Prev	Next>		<<First	<Prev	Next>	Last>>