Masking an amount
-
Hello to all. I have a DTS job that creates a flat text file including an amout field (money) when the column is written into the text file it has to be maked as follows:
1. Amonut 25.23 or 485.56
2. Mask should be: 0000002523 (ten characters with leading zeros and no decimal point)
or 0000048556
I did masked the amount, but the decimal point still is in the column.
3. My mask: 0000025.23 (ten characters with leading zeros, BUT decimal point is shown)
or 0000485.56
Any help will be appreciated to mask the amount correctly and get rid of the decimal point. Thank you
-
What about writing the masked figure into a VARCHAR(11) variable then REPLACE the decimal in the variable and append that to the string you are writing.
DECLARE @Var VARCHAR(11)
SET @Var = '0000485.56'
SET @Var = REPLACE(@Var,'.','')
SET @Var = '0' + @Var
SELECT @Var
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I take it that you are using activex script to transform the data before populating the text file. If so, use :
Replace(String, FindSubstring, ReplaceSubstring, Start, Count, Compare)
For you example it would be Replace(amountcolumn, ".", "") which will strip out the decimal point. Or replace with a zero by amending the code above.
------------------------------
The Users are always right - when I'm not wrong! -
You could do it in SQL:
create table #t(amount money not null)
insert #t select 25.23 union select 485.56
select
amount,
replicate('0',11-len(convert(varchar,amount)))+replace(convert(varchar,amount),'.','')
from
#t
drop table #t
Important:
The above will return NULL if amount > 99 999 999.9999
( You have to tweak it a little if that is the case )
/rockmoose
You must unlearn what You have learnt -
Thank you guys!! with all your input I solve the issue. I used a combination of your sugestions and it works perfectly. Thank again!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply