Transform text field to money - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transform text field to money

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2005-10-26 : 11:30:58

I have a text column in my database with values simular to the below

00000000-505
000000018500
0000000-4495

I need to convert them by removing all leading zeros, determining if the value is negative (-), and inserting the decimal points.

Example:
00000000-505 -> -$5.05
000000018500 -> $185.00
0000000-4495 -> -$44.95

How can I create a trigger to do this for me, when the data is imported; or should I make a stored procedure to run when when I import the data. This data is found in a text file that is imported to the table daily. Any suggestions and or help would be greatly appriciated.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-26 : 12:09:15

One way:

set nocount on
declare @yourTable table (yourColumn varchar(100))
insert into @yourTable (yourColumn)
	select 	'00000000-505' union
	select 	'000000018500' union
	select  '0000000-4495'


select 	yourColumn 'input',
	cast(cast(stuff(yourColumn,1,patindex('%[^0]%',yourColumn)-1,'') as int)as money) * .01 'to money'	
from @yourTable

Nathan Skerl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 00:31:36

Why did you store money values in text columns?
You should use proper datatype to avoid this conversion

Madhivanan

Failing to plan is Planning to fail

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-28 : 02:31:56

I assume (hope) that you meant varchar when you said "text column." If its contained within an incoming csv/txt file he would have no choice but to convert it (he did say "import").

Nathan Skerl

Kristen
Test

22859 Posts

Posted - 2005-10-28 : 07:04:22

Assuming this is a staging table that stuff can get chucked into, and you want to clean it up before putting it into the real tables ...

My inclination would be to allow the stuff to be thrown into the table "willy nilly". It depends on what your source is for the data, but in my experience over time other folk say "Can you import my data too" or "Can we now get the data from here instead?" and a don't-care staging table helps with that.

No NOT NULL columns. Columns that are bigger than the biggest data. Etc.

I would also add a couple of columns:

Status - 0=Good, 1=Bad (maybe 9=Unchecked or somesuch)
Message - A human readable message of why the row is bad - "Money column format error"
BatchNo - the batch that this row was processed as. (NULL or 0 = Not yet processed)

Then I would do the following:

SELECT @NextBatch = MAX(BatchNo)+1 FROM MyTable

UPDATE MyTable
SET BatchNo = @NextBatch
WHERE BatchNo IS NULL

this will allocate the next batch number to all rows that have not been processed. More rows can now be added to the table, we won't get into a muddle with "what are we processing in this batch" etc.

Then I would do

UPDATE MyTable
SET Status = 1,
Message = COALESCE(Message+', ', '') + 'Money column format error'
WHERE BatchNo = @NextBatch
AND Status IS NULL -- Not yet found any errors
AND MyMoneyColumn NOT LIKE 'SomePattern'

Repeat the UPDATE for however many "quality tests" you have.
Convert the MoneyColumn (and others) to the desired format whilst you are at it - provide additional columns for this, or a temporary table, whatever.

Then set the "good ones" to Status=0


UPDATE MyTable
SET Status = 0
WHERE     BatchNo = @NextBatch
      AND Status IS NULL -- Not yet found any errors

Eventually you will have "clean data" and the row marked with Status=0 - then import those into your table.

Print out a list of any rows where Status<>0 - i.e. the errors

Delete the batch immediately, or sometime later if its helpful to have some leeway to resolve issues.

I don't think I would use a trigger. You MIGHT only want to process the batch if it contains ZERO errors - i.e. you will want to NOT run the update to live tables if any of the rows in the batch have Status<>0

Also, if you want to allow the Import process to happen at "any time" from "whatever source" you might want to run the Error Check and Copy Staging-to-Live on a schedule - hourly perhaps? Or using a Stored Procedure which each Import routine launches when it has finished.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources