sql server programming: I have a table where the user enters a coupon number for each record. The
coupon number is usually an integer, however the user sometimes enters a
suffix charater to the coupon number (i..e "M") so the coupon field is a
nvarchar type. Hence the coupon number list might look like this:

567
590M
678
4578
4579
4580
4581M

I need to dislay the coupons in ascending order, however the sort is not
sorting by the corresponding "integer" value because teh string values sort
differently. Hence the list above sorts as follows:

4578
4579
4580
4581M
567
590M
678

Is there a SQL function that strips out the non-numerical characters in a
string, leaving only the numerical characters. I can then convert this to an
"int" type and sort. I haev tried "BTRIM" but SQL does not recognize this.
I have tried just CONVERT(int, CouponNumber), but SQL chokes on the "M" and
cannot process these. I have managed to create a long CASE function that
looks at the last character and removed it, but this is slow and will not
work if they use two or more suffix characters. Is there an easy way to
sort based on the the integer value of a string?

help?

-Stephen

CAST (REPLACE (coupon_nbr, 'M','') AS INTEGER)

If there are more than one non-numeric, nest the REPLACE () functions.
I would also fix the DDL to avoid this problem; what good is it to mop
the floor and not fix the leak?

I use the function described here:

http://milambda.blogspot.com/2005/03/fail-safe-conversion-to-integer.html

Feel free to look up the rest. :)