SQL: CharToNum

*
* If a character fields is used as a number field, it will not sort properly as

* "200" comes before "5" with string sorting. This function converts all values

* such that values that are entirely numeric will sort correctly and before

* values that contain other characters.

*

*/

CREATE FUNCTION CharToNumber(@MyValue varchar(15)) RETURNS char(15) AS

BEGIN

IF ISNUMERIC(@MyValue) = 1

begin

DECLARE @MyValLength int

SET @MyValLength = len(@MyValue)



IF @MyValLength > 15

SET @MyValue = left(@MyValue,15)

ELSE IF @MyValLength < 15

SET @MyValue = space(15-@MyValLength) + @MyValue

END



RETURN @MyValue

END

GO