How to implement MAX function on a text column in SQL Server?

Question

I'm using SQL Server 2005 and have a column that contains serial numbers, which are nvarchar(50).

My problem is selecting max(serial_no) from the table. The serial numbers used to have a length of 7 only but new ones are now 15. Whenever I select the max, I get a result with a length of 7, which means that data is old. I also can't filter it to only select from records which have a length of 15 because then i'll miss some other data on my query.

Old serial numbers look like this...

'SNGD001'

..., and new ones look like this:

'SN14ABCD0000001'

Edit: I tried creating a dummy table without the old serial numbers (5 characters long), and I'm getting correct results.

Post your table definition. Unless you store numbers as strings your problem description does not make sense. And if they are strings, how do you want to define `MAX`? — PM 77-1, Dec 20 '14 at 02:51
@fejese Old Serial numbers look like this "SNGD001" and new ones are "SN14ABCD0000001". — sabre, Dec 20 '14 at 02:56
How do you *compare* them? If you had just two "SNGD001" and "SN14ABCD0000001", which one would've been selected as `MAX` and why? — PM 77-1, Dec 20 '14 at 02:57
Are the new serial numbers always considered to be larger numbers than the old ones? — grin0048, Dec 20 '14 at 02:58
@PM77-1 max would be the highest serial number, since the only thing that changes on the serial number is the last 3(7) digits then i should be getting the highest, except i'm getting the highest of the old serial number — sabre, Dec 20 '14 at 02:59
@grin0048 yes, the new one just added more characters and has a longer running number, after that the 7 digit running number will be the only thing changing(increasing) — sabre, Dec 20 '14 at 03:01

score 1 · Accepted Answer · answered Dec 20 '14 at 04:15

As has been mentioned, your question is a bit hard to follow. If the max value could be either one of your old serial numbers or one of your new ones, I believe the following should do the trick:

SELECT MAX(RIGHT('0000000' + REVERSE(LEFT(REVERSE(YourTextColumn), PATINDEX('%[a-z]%', REVERSE(YourTextColumn)) - 1)), 7))
FROM YourTable

It finds the first non numeric character from the right keeping everything to the right of that. It then left zero pads the resulting numeric string to 7 characters and applies the MAX function.

PERFECT! Thank you so much, I even forgot to say what the max value might be but you're right it could be a serial number with an old length. Thanks again. — sabre, Dec 20 '14 at 04:27

score 0 · Answer 2 · answered Dec 20 '14 at 04:08

Your question is a little tough to follow without good sample data to get a bearing on. I suggest for future, you show a few more examples of data to get better context, especially with sequencing. Now, your desire to get the MAX() of a "serial_no" from your table appears you need so you get detect the next sequential serial number to assign. However, your serial number appears to be a concatenation of a prefix string and then sequential. So, if I were to look at your brief data MIGHT HAVE BEEN along the lines of (last 3 digits are the sequential serializations)

SNGD001
SNGD002
SNGD003
...
SNGD389, etc...

and your new data with the last (last 7 digits are sequential serializations)

SN14ABCD0000001
SN14ABCD0000002
SN14ABCD0000003
...
SN14ABCD0002837

If this is correct, then you basically need to look at the max based on the leading 3 or 8 characters of the string PLUS the converted suffix numeric sequence. For starters, lets go with that to see if we are on the correct track or not, then you can easily concatenate the prefix and sequence number together at the end for determining the next available number.

So, based on the above samples, you may want to know that for each prefix, the last number of

SNGD389   and 
SN14ABCD0002837 respective per their prefix

If the above is correct, I might start with...

select
      case when LEN( RTRIM( yt.serial_no )) = 7
           then LEFT( yt.serial_no, 4 )
           else LEFT( yt.serial_no, 8 ) end as SerialPrefix,
      MAX( case when LEN( RTRIM( yt.serial_no )) = 7
                then CONVERT(INT, RIGHT( yt.serial_no, 3 ))
                else CONVERT(INT, RIGHT( yt.serial_no, 7 )) end ) as SerialSequence
   from
      YourTable yt
   group by
      case when LEN( RTRIM( yt.serial_no )) = 7
           then LEFT( yt.serial_no, 4 )
           else LEFT( yt.serial_no, 8 ) end as SerialPrefix

Which would result in (based on sample data I presented)

SerialPrefix    SerialSequence
SNGD            389
SN14ABCD        0002837

Of which since the serial sequence column being numeric, you could add 1 to it, then left-zero fill a string and concatenate the two back together such as to create

SNGD390
SN14ABCD0002838

How to implement MAX function on a text column in SQL Server?

2 Answers2