I recently had to write a T-SQL function which cleans up all non numeric characters from a varchar variable and returns the int value of the remaining numeric characters. If no numeric characters exist, the function should return zero. I thought it would be nice to share this with you and I hope you find this useful ;-) Here is the function:
CREATE FUNCTION [dbo].[GetIntFromVarchar]
(
@originalString varchar(50)
)
RETURNS int
AS
BEGIN
-- declare local variable to hold numeric characters
-- and assign default value of empty string
DECLARE @retVal varchar(50)
SET @retVal = ''
-- declare local variable to loop through @originalString
-- and assign initial value of 1
DECLARE @loop int
SET @loop = 1
-- loop through every character in @originalString
WHILE @loop <= LEN(@originalString)
BEGIN
IF ISNUMERIC(SUBSTRING(@originalString, @loop, 1)) = 1
BEGIN
-- if numeric character found, append it to @retVal
SET @retVal = @retVal + SUBSTRING(@originalString, @loop, 1)
END
SET @loop = @loop + 1 -- increment value of @loop by 1
END
-- set @retVal to '0' if no numeric characters found
IF @retVal = ''
BEGIN
SET @retVal = '0'
END
-- return int value of numeric characters found
-- by casting @retVal to int
RETURN CAST(@retVal as int)
END
Once you have created this function, execute below T-SQL statements to verify the same:
PRINT dbo.GetIntFromVarchar('R1A2J3') -- returns 123
PRINT dbo.GetIntFromVarchar('COOL456CODER') -- returns 456
PRINT dbo.GetIntFromVarchar('789') -- returns 789
PRINT dbo.GetIntFromVarchar('GEEK') -- returns 0
Cheers,
Raj
~~~ CODING FOR ETERNITY !!! ~~~