Ravi,
Knight 4 to Pawn's 5!
Sorry, being serious for a minute, you'd need more info to solve
this problem. Your example implies that something without a number after
it still counts for a value of 1, i.e.
> and 3 if I am searching for WT.
Correct? What is the extent of the two letter combos? Are we talking
just WT, BW, ME, SY, WE, OT and IT or are we talking about the whole
space of AA to ZZ?
You say the table is huge? 1 million records? 10 million? Just a rough
ball park are these Var chars indexed?
Just off the top of my head and without knowing what the actual context
of the problem is I have to say it looks very difficult to do in pure
SQL, not impossible just very difficult. Can you not use some glue code?
Perl would be an ideal language, PHP would also be fine, any language
would be better than SQL to be honest as it's just the wrong tool.
I have a table with a varchar column that stores data in this pattern:
<number><one_white_space><string><one_white_space><hyphen_symbol>
The pattern may be repeated upto 5 times in same cell, but the string will
be different in each case. Hence there will not be a value like this:
1 BW - 2.5 BW -
1 WT -
1 BW - 1 ME -
1 BW - 1 ME - 1.5 SY -
1 BW -
1 WT -
1 OT - 2.5 WE -
1 OT - 1 SY -
1 WT -
1 IT - 1 OT -
I need a regular exp or any other query to fetch sum of numbers before a
string. For example the result should be 2.5 if I am searching for SY and 3
if I am searching for WT.
I tried a bit, but could not succeed. I am using 4.1.14-standard-log.
The table is huge, hence the query should be streamlined enough.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]