Data conversion during import

Question

Data conversion during import

Wilfredo Santana

SSC Enthusiast

Points: 110
More actions
October 10, 2004 at 8:21 am

#60539

I am importing a text file from a legacy system. This file contains a field that is the paid amount but comes with characters when is a negative value. 000123Y = -12.39. I am planning to create a new field in the table and convert the data into the new field as the correct value. I Need to keep the original data since is history data from a client. What is the best data type for this field? What is the best approach to do the conversion from 000123Y to -12.39? ( Y = -9, T = -8, G = -7... } = -1)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Wow, bizarre format.

So is the legacy field always 7 characters long, with an implied decimal point between characters 5 and 6? If so, you should store this data in a char(7) field.

As for the second part of your question, can you confirm that

0001237 represents 12.37

and

000123G represents -12.37?

Is the negative indicator (by this, I mean the letter 'Y', 'T' etc) always the final character of the legacy string?

Once you have clarified these points, I can suggest how to perform the conversion.

Regards

Phil

Wilfredo Santana SSC Enthusiast Points: 110 More actions · Answer 2

Thanks for you assistance. Your questions:

1.) Always comes in 7 characters long.

2.) Correct. The letter means the last digit value.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

This should help. Not the most elegant, but neither is the format. You will have to amend the letters in the @negs string to correspond properly with your format.

You can check that it works by setting the value of @test-2 and running - the final select returns a converted number.

--Declare variables

declare @negs char(10)

declare @test-2 char(7)

declare @index float

declare @testconv currency

declare @char7 char(1)

--Set a string to hold the characters that represent negative single-digit integers, in order 012...9

set @negs = 'NMLKJIHGTY'

--Assign @test-2 for testing purposes

set @test-2 = '0001239'

set @char7 = right(@test,1)

--Set @index to the position of the letter in the @negs string

set @index = charindex(right(@test,1),@negs,0)

if @index = 0 --string not found, therefore number is positive

set @testconv = cast((left(@test,7) ) as float)

else

set @testconv = (cast (left(@test,6) as float) * 10 + (@index - 1)) * -1

select @testconv / 100

Regards

Phil

David Burrows SSC Guru Points: 65144 More actions · Answer 4

Not pretty but.....

DECLARE @value char(7)

SET @value = '000123Y'

SELECT SUBSTRING('0-',PATINDEX('%[A-Z]%',SUBSTRING(@value,7,1))+1,1)

+ SUBSTRING(@value,1,5)

+ '.'

+ SUBSTRING(@value,6,1)

+ SUBSTRING('01234567890123456789',CHARINDEX(SUBSTRING(@value,7,1),'0123456789.......GTY'),1)

you can use the result as is or CAST it if you prefer

p.s. you will need to replace the dots in .......GTY with the corresponding letters

Far away is close at hand in the images of elsewhere.
Anon.