August 4, 2005 at 12:43 pm
We currently have mainframe data we need to load into SQL Server. The mainframe data is stored in EBCDIC Format and negative numbers are represented using the EBCDIC Conversion (-0 = }, -1 = J. -2 = K, -3 = L, -4 = M, -5 = N, -6 = O, -7 = P, -8 = Q & -9 = R).
I know Objects (i.e., Tables) in SQL Server store data using data types which define the kind of data (character, integer, decimals, floats, money, datetime, etc.) the Objects can hold. Data stored in SQL Server must be compatible with one of these base data types.
How can we convert these negative numbers so that SQL Server would recognize the negative values?
My first suggestion would be to have the data converted (during the conversion process) based on the last position in the numeric field. For, example if the last position indicates a negative then store -200.00 or if the last position indicates a positive then store +200.00 or just 200.00.
A related question, the mainframe data is stored in EBCDIC Format, is Personal Computer Data stored in ASCII Format? Or is SQL Server Data eventually stored in ASCII Format?
Thanks in advance for you help, Kevin
August 4, 2005 at 1:16 pm
How are you getting the data into the SQL Server? If the mainframe data can be extracted in clear text (not packed decimal or zoned decimal) then a utility like FTP can do the conversion between EBCDIC and ASCII. But this means your -9 would show up in the clear text file as -9, not 'R'. There are probably utilities available from the internet to 'unpack' the data and make it suitable for import if you didn't want to unpack it on the mainframe before transfering it.
August 5, 2005 at 2:21 pm
This may need to be adjusted to your situation, but I have used the following function for a while.
One note, it accepts a character string and returns a character string. You might want to convert the string to a number in the function or in the code calling the function.
----------------------------------------------------------------
create function fnOverpunch(@Overpunch varchar(30), @Scale int)
returns varchar(30) as
begin
declare @Result varchar(30)
, @Char char(1)
, @CharIndex int
, @Multiplier int
set @Overpunch = rtrim(@Overpunch)
if @Overpunch is null or @Overpunch = ''
return null
if isnumeric(@Overpunch) = 1
set @Result = @Overpunch
else begin
set @Char = right(@Overpunch, 1)
set @Overpunch = left(@Overpunch, len(@Overpunch) - 1)
set @CharIndex = charindex(@Char, '{ABCDEFGHI')
if @CharIndex > 0 begin
set @Result = @Overpunch + convert(char(1), @CharIndex - 1)
set @Multiplier = 1
end
set @CharIndex = charindex(@Char, '}JKLMNOPQR')
if @CharIndex > 0 begin
set @Result = @Overpunch + convert(char(1), @CharIndex - 1)
set @Multiplier = -1
end
end
if @Scale = 0
return @Result
set @Result = left(@Result, len(@Result) - @Scale) + '.' + right(@Result, @Scale)
if @Multiplier = -1
set @Result = '-' + @Result
return @Result
end
-- --
-- -- --USAGE
-- -- SELECT dbo.fnOverpunch('123R', 2)
-- -- -- returns 12.39
-- -- SELECT dbo.fnOverpunch('123R', 2)
-- -- -- returns 1239
---------------------------------------------------------------------
August 5, 2005 at 2:49 pm
After the previous post, I actually looked at the code and found the obvious error in the last usage line. I also thought about the value of returning a varchar rather than a number.
When we were using this function earlier we were importing text files and storing text values so the varchar fit our needs.
I have changed the function to return a float and corrected the usage examples. Again, this could be modified to suit your needs by returning an integer or a decimal, whatever.
Feel free to improve upon this or totally disregard it.
-----------------------------------
--As written, this function accepts a string and returns a float
-- To convert text fields in a text file, can return a varchar(30) by
-- changing the RETURNS and RETURN lines
CREATE FUNCTION fnOverpunch(@Overpunch varchar(30), @Scale int)
RETURNS float
AS
BEGIN
declare @Result varchar(30)
, @Char char(1)
, @CharIndex int
, @Multiplier int
set @Overpunch = rtrim(@Overpunch)
if @Overpunch is null or @Overpunch = ''
return null
if isnumeric(@Overpunch) = 1
set @Result = @Overpunch
else begin
set @Char = right(@Overpunch, 1)
set @Overpunch = left(@Overpunch, len(@Overpunch) - 1)
set @CharIndex = charindex(@Char, '{ABCDEFGHI')
if @CharIndex > 0 begin
set @Result = @Overpunch + convert(char(1), @CharIndex - 1)
set @Multiplier = 1
end
set @CharIndex = charindex(@Char, '}JKLMNOPQR')
if @CharIndex > 0 begin
set @Result = @Overpunch + convert(char(1), @CharIndex - 1)
set @Multiplier = -1
end
end
if @Scale = 0
return Cast(@Result as float) * @Multiplier
set @Result = left(@Result, len(@Result) - @Scale) + '.' + right(@Result, @Scale)
--if @Multiplier = -1 --Used to return a varchar(30) result
--set @Result = '-' + @Result
return Cast(@Result as float) * @Multiplier
--return @Result --Used to return a varchar(30) result
END
-- --
-- -- --USAGE
-- -- SELECT dbo.fnOverpunch('123R', 2)
-- -- -- returns -12.39
-- -- SELECT dbo.fnOverpunch('123R', 0)
-- -- -- returns 1239
-- -- SELECT dbo.fnOverpunch('123D', 2)
-- -- -- returns 12.34
-- -- SELECT dbo.fnOverpunch('123D', 0)
-- -- -- returns 1234
-------------------------------------
January 10, 2007 at 11:06 am
Hello,
I liked this function, but it returned NULL when a numeric value was sent. After poking around a bit I found the @multiplier value is only set when IsNumeric is false. I altered the function:
set
ANSI_NULLS ONset
QUOTED_IDENTIFIER ONgo
--As written, this function accepts a string and returns a float
-- To convert text fields in a text file, can return a varchar(30) by
-- changing the RETURNS and RETURN lines
ALTER
FUNCTION [dbo].[fnOverpunch](@Overpunch varchar(30), @Scale int)RETURNS
floatAS
BEGIN
declare @Result varchar(30) , @Char char(1) , @CharIndex int , @Multiplier int set @Overpunch = rtrim(@Overpunch) if @Overpunch is null or @Overpunch = '' -- Function is done return NULL if isnumeric (@Overpunch) = 1 BEGIN -- Numeric set @Result = @Overpunch SET @Multiplier = 1 END -- Numeric ELSE begin -- not numeric set @Char = right(@Overpunch, 1) set @Overpunch = left(@Overpunch, len(@Overpunch) - 1) set @CharIndex = charindex(@Char, '{ABCDEFGHI') if @CharIndex > 0 begin set @Result = @Overpunch + convert(char(1), @CharIndex - 1) set @Multiplier = 1 end set @CharIndex = charindex(@Char, '}JKLMNOPQR') if @CharIndex > 0 begin set @Result = @Overpunch + convert(char(1), @CharIndex - 1) set @Multiplier = -1 end end -- not numeric if @Scale = 0 BEGIN return Cast(@Result as float) * @Multiplier END set @Result = left(@Result, len(@Result) - @Scale) + '.' + right(@Result, @Scale) --if @Multiplier = -1 --Used to return a varchar(30) result --set @Result = '-' + @Result return Cast(@Result as float) * @Multiplier --return @Result --Used to return a varchar(30) resultEND
-- --
-- -- --USAGE
-- -- SELECT dbo.fnOverpunch('123R', 2)
-- -- -- returns -12.39
-- -- SELECT dbo.fnOverpunch('123R', 0)
-- -- -- returns 1239
-- -- SELECT dbo.fnOverpunch('123D', 2)
-- -- -- returns 12.34
-- -- SELECT dbo.fnOverpunch('123D', 0)
-- -- -- returns 1234
It works just fine for me now. Thanks for all of your hard work!
Bill.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply