Archives of the TeradataForum
Message Posted: Thu, 04 Jan 2007 @ 10:07:26 GMT
<-- 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
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 ;
- 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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|