Monday, July 18, 2011

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

No comments:

Post a Comment

LIveCharts2: Charts for Windows and web

Charts for Windows and web including .Net MAUI  LiveCharts - LiveCharts2 (lvcharts.com)