October 31, 2013

Unpack Decimal (Comp-3) Using SQL Function


Finally after creating a custom component for ssis I came into thinking how to do the same using a sql function after research and plenty of Google I found some similar cases and with some ingenuity modify the code to my needs after all if the ops dba doesn't like the deployment of a custom dll then the alternative is a sql function So here it is folks... Ah you still need to load the file into an table using varbinary (comp-3) format and as an added bonus submit the scale value, the next step is to create a few functions that do the opposite convert a numeric value into comp-3 format, but that is just another history.
Cheers.

CREATE FUNCTION [dbo].[udf_UnpackDecimal] (
@InputStr varbinary(50) = NULL
,@Scalar int = 0

)

RETURNS varchar(2000)
AS
BEGIN
DECLARE @UnpkgStr varchar(2000)
DECLARE @pos int
DECLARE @length int
DECLARE @temp int
DECLARE @nibble1 int
DECLARE @nibble2 int
DECLARE @hexstring char(16)
DECLARE @DecPos int

SET @UnpkgStr = ''
SET @pos = 1
SET @length = DATALENGTH(@InputStr)
SET @hexstring = '0123456789ABCDEF'

WHILE (@pos <= @length)
BEGIN
SET @temp = CONVERT(int, SUBSTRING(@InputStr, @pos, 1))
SET @nibble1 = FLOOR(@temp / 16)
SET @nibble2 = @temp - (@nibble1 * 16)

SET @UnpkgStr = @UnpkgStr + SUBSTRING(@hexstring, @nibble1 + 1, 1)

IF @pos < @length 
SET @UnpkgStr = @UnpkgStr +SUBSTRING(@hexstring, @nibble2 + 1, 1)
ELSE
IF SUBSTRING(@hexstring, @nibble2 + 1, 1) = 'D'
SET @UnpkgStr = '-' + @UnpkgStr 

SET @pos = @pos + 1
END

set @UnpkgStr = cast (cast(@UnpkgStr as decimal)as varchar(200))

Set @DecPos = (DATALENGTH(@UnpkgStr)+1)-@Scalar


RETURN isnull (Stuff(@UnpkgStr,@DecPos,0,'.'),'0')

END

1 comment:

Contact Form

Name

Email *

Message *