Monday, 18 July 2016

How to get decimal value in a string using sql server

CREATE FUNCTION [dbo].[udf_GetNumericValue]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9.]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9.]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END

-- SELECT   dbo.[udf_GetNumericValue]('CAD 7 810.50')

Output : 7810.50

No comments:

Post a Comment