Max value in the table

Question

Max value in the table

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Evil Kraig F (6/21/2012)
If these fields are all the exact same length than what's posted above by Lynn will work. I'm afraid I don't necessarily agree with Lynn about 01 vs. 010 vs. 001, but that'll depend exactly on your formatting expectations.
That said, if these fields AREN'T always the same length/positions, then you're going to need to get pretty fancy with Reverse(), testing if the . or the - is found earliest in the reversed line (so you can figure out if you're dealing with appended data to the value you want), then trimming the string and re-reversing depending on what you find.
So, before I go through all that... are those ****s considered variable length wildcards or are these values always at the exact same position in the string?

I gave an answer based on a lack of information from the OP, and based on the query, I would also assume that positions 39,40,41 are pretty much fixed.

With more complete info from the OP a better answer is possible.

ssc_san SSCommitted Points: 1563 More actions · Answer 2

Thank you Lynn.

You haven't answered the question, how does a SQL query know that '01-' is supposed to be '001' not '010'? You have said that '010' is not a valid value, at this time but that it could change.

As your code appended the '0', will work. Very less chance of adding additional values.

Thanks.

ssc_san SSCommitted Points: 1563 More actions · Answer 3

Evil Kraig F (6/21/2012)
If these fields are all the exact same length than what's posted above by Lynn will work. I'm afraid I don't necessarily agree with Lynn about 01 vs. 010 vs. 001, but that'll depend exactly on your formatting expectations.
That said, if these fields AREN'T always the same length/positions, then you're going to need to get pretty fancy with Reverse(), testing if the . or the - is found earliest in the reversed line (so you can figure out if you're dealing with appended data to the value you want), then trimming the string and re-reversing depending on what you find.
So, before I go through all that... are those ****s considered variable length wildcards or are these values always at the exact same position in the string?

****s are fixed position and length. As applications are being configured, they will change the method of user input to drop down selection. So this is just for the time being.

Thanks for the reply Kraig .

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

ssc_san (6/21/2012)
Thank you Lynn.
You haven't answered the question, how does a SQL query know that '01-' is supposed to be '001' not '010'? You have said that '010' is not a valid value, at this time but that it could change.
As your code appended the '0', will work. Very less chance of adding additional values.
Thanks.

The question, however, is this the correct action to take or should the data be corrected since the user erred during input.

ssc_san SSCommitted Points: 1563 More actions · Answer 5

The question, however, is this the correct action to take or should the data be corrected since the user erred during input.

As they are going to change the input method, I have to address that in the SQL code for now. It is not going to be changed even if it is entry error.

Thanks.