Handy for grabbing a number from a string or varchar value
CREATE FUNCTION udf_StripNonnumericChars
(
@OrigString VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
/*
32 is a space
48 to 57 are our numbers 0-9
65 to 90 are the capital letters A-Z
97 to 122 are the lowercase letters a-z
*/
DECLARE @NewString VARCHAR(255)
DECLARE @Len INT ,
@Ctr AS INT ,
@Ctr2 AS INT ,
@Char AS VARCHAR(1)
SELECT @Len = LEN(@OrigString)
SELECT @NewString = ''
SELECT @Ctr2 = 1 ,
@Ctr = 1
WHILE @Ctr <= @Len
BEGIN
SELECT @Char = SUBSTRING(@OrigString, @Ctr, 1)
IF (ASCII(@Char) BETWEEN 48 AND 57) OR ASCII(@Char) = 46
BEGIN
SELECT @NewString = @NewString + @Char
SELECT @Ctr2 = @Ctr2 + 1
END
SELECT @Ctr = @Ctr + 1
CONTINUE
END
IF @Ctr2 = 1
SELECT @NewString = NULL
RETURN @NewString
END
GO
Jordon Pilling | Heavencore Administrator
|
