Friday, October 15, 2010

Convert Varchar to Numeric in SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER Off
GO
alter FUNCTION [dbo].[Return_Example]
(

@a numeric(18,4),
@b numeric(18,4)
)
RETURNS numeric(18,3)
AS
BEGIN
declare
@division int,
@remainder int,
@Num2 varchar(100),
@c numeric(18,4)
set @division=floor(@a/@b)
set @remainder=@a%@b
--Here output will be a concatenated
set @num2 = cast(@division as varchar) + '.'+ cast(@remainder as varchar)
set @c=cast(@Num2 as numeric(18,3))
RETURN @c
END

-------------- Run As ---------

declare @a as varchar(100)
set @a = dbo.Return_Example(59,18)
print @a



OUTPUT :
3.500