String operation: Get biggest and lowest Number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

String operation: Get biggest and lowest Number

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-21 : 14:56:28

I have a 6 digit string of numbers and need to establish the highest and the lowest number. The algorithm that I did is 3 leveled 'case when' expression, that is rather lengthy but it works.

Unfortunately this was just the first step. Now I need to do some more calculations using these complex terms, and the length of the expression got too big for the computed column formula to hold. I pasted it from simple text and it took 2/3 of the formula.

So, in order to get it more lean, I have to get a better algorithm for the principle problem, getting the biggest and lowest number.

Is there a way to build the formula up, by splitting it up into several parts? I can't create formula based column "lowest" and refer to this value in another formula based column... that would be sooo cool, but SQL doesn't seem to like that.

Ufff, after all the time it costs to create that massive "case when..." expression.

Any idea's?
Martin

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-21 : 15:18:47

It's not very clear what you're trying to do...Can you show sample data and expected output.

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-21 : 16:08:35

The problem regards only the set-up of the columns of that table:

pattern =(6-digit string of numbers) eg.243156
lowest= (calculated column, figuring out the highest 1-digit number in the string "pattern") eg.1
highest= (calculated column, figuring out the highest 1-digit number in the string "pattern") eg.2

the expression that works, but that leads to a much to long formula is - brought just to the first level to keep it simple:

lowest(of the first two digits) = CASE WHEN CAST(SUBSTRING(pattern,1,1))< CAST(SUBSTRING(pattern,2,1)) THEN CAST(SUBSTRING(pattern,1,1)) ELSE CAST(SUBSTRING(pattern,2,1))END

Then you check this result with digit #3 and #4 of pattern, and compare the result to #5 and #6, melt all in one, to get one single expression that returns the lowest digit.

Hope it is a bit more clearer now.

Finally I need to get this value:

cast(bund1 as nvar)+'l'+cast(span as nvar)+'.gif' with:

bund1=substring([playpattern],(1),(1))- lowest

span=highest-lowest

so, once you merge all this expressions into one, that thing grows to a size, one can't insert into the calculation column.

barnabeck
Posting Yak Master

236 Posts

Posted - 2010-02-21 : 16:11:53

of course the higest number in that example above would be 6 and not 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:32:49

seems like wht you need is


SELECT numberfield,MIN(val),MAX(val)
FROM
(SELECT t.numberfield,SUBSTRING(t.numberfield,v.number,1) AS val FROM YourTable t
 CROSS JOIN master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND LEN(t.numberfield)
)m
GROUP BY numberfield

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources