Calculating total value of Numeric values assigned

问题:

This is kind of complicated, so bear with me.

I've got the basic concept figured out thanks to THIS QUESTION

SELECT LENGTH(col) - LENGTH(REPLACE(col, 'Y', ''))

Very clever solution.

Problem is, I'm trying to count the number of instances of a string token, then take that counter and multiply it by a modifier that represents the string's numeric value. Oh, and I've got a list of 50-ish tokens, each with a different value.

So, take the string "{5}{X}{W}{b/r}{2/u}{pg}"

Looking up the list of tokens and their numeric value, we get this:

{5}   5
{X}   0
{W}   1
{b/r} 1
{2/u} 2
{pg}  1
....  ....

Therefore, the sum value of the string above is 5+0+1+1+2+1 = 10

Now, what I'm really looking for is to a way to do a Join and perform the aforementioned replace-token-get-length trick for each column of the TokenValue table.

Does that make sense?

Psuedo-SQL example:

SELECT StringColumn, TotalTokenValue
???
FROM TableWithString, TokenValueTable

Perhaps this would work better as a custom Function?

---

EDIT

I think I'm about halfway there, but it's kind of ugly.

SELECT        StringColumn, LEN(StringColumn) AS TotalLen, Token, 
                { fn LENGTH(Token) } AS TokenLength, TokenValue, 
                { fn REPLACE(StringColumn, Token, '') AS Replaced, 
                { fn LENGTH(Replaced) } AS RepLen,
                { TotalLen - RepLen / TokenLength} AS TokenCount }, 
                { TokenCount * TokenValue} CalculatedTokenValue
FROM            StringTable CROSS JOIN
                         TokenTable

Then I need to wrap that in some kind of Group By and get SUM(CalculatedTokenValue) I can picture it in my head, having trouble getting the SQL to work.

回答1:

If you create a view like this one :

Create or replace view ColumnsTokens as 
  select StringColumn, Token, TokenValue, 
   (length(StringColumn) - length(replace(StringColumn, token, ''))) / length(Token) TokenCount 
from StringTable 
join TokenTable on replace(StringColumn, Token, '') <> StringColumn ;

that will act as a many-to-many relationship table between columns and tokens, I think you can easily write any query you need. For example,this will give you the total score :

select StringColumn, sum(TokenCount * TokenValue) TotalTokenScore
   from ColumnsTokens 
group by StringColumn ;

(You're only missing the StringColumns with no tokens)