August 7, 2006 at 7:59 am
I have a view whose resultset is captured and sent via bcp to a mainframe as a flat file. The file needs to have certain data formatted in a specific way. I need to place leading 0s in front of some fields and I'm not sure how to do this within my view. For example a value of $100 to be passed into a 10 byte field needs to be passed as 0000010000 (the mainframe is expecting the last 2 digits to be the cents, so i need no punctuation). Is there an easy way of creating a number of leading 0s based on the size of the expected output versus the size of the data length of each individual record returned within this view?
Any help would be appreciated. Thanks in advance
justin
August 7, 2006 at 8:17 am
it's for .NET 2.0/SQL 2005:
create a CLR function that would take a str like "$100' as a input parameter. Next:
1. process the str: get rid of $ and a dot; IF no dot - add 2 0's
2. find the string length; compare it to the targeted 10-char string; add needed 0's
3. return 10-char long string '0000010000'
4. [compile; deploy assembly to a SQL server]
August 7, 2006 at 8:33 am
Hi Justin,
Given that you're using SQL 2000 you cannot create a CLR function but you can do this easily enough using a standard user defined function that accepts an numeric value.
Like so:
create
function dbo.fn_GetPaddedNumeric (@number numeric)
returns varchar
(10)
as
begin
declare @result varchar(10)
set @result = replace(convert(varchar(11),@number),'.','')
while len(@result) < 10
begin
set @result = '0' + @result
end
return @result
end
Then, inside the view, you'd do:
select col1, col2, dbo.fn_GetPaddedNumeric (col3)
from my_table
Col3 would be the columm you'd want to convert to having leading zero's.
Hope that helps,
August 7, 2006 at 8:40 am
Karl, could I take it a step further and somehow code it to be used for varying lenghths? Some of my expected value lengths may be 10, but others are different, based on the data.
Thanks for your response!
Justin
August 7, 2006 at 10:32 am
Hi Justin,
Yes, that would be quite easy. Just add another parameter called @length (datatype tinyint).
Then, inside the function, the while statement can be:
while len(@result) < @length
When you call the function you'll just need to pass the length through to it.
August 7, 2006 at 11:07 pm
declare @m money
set @m =100
select replace(STR(@m*100), space(1), '0')
_____________
Code for TallyGenerator
August 9, 2006 at 8:09 am
The simpliest example of leading zero creation I know of is:
select
replicate('0',@MaxLen-len(cast(MnyVal as varchar(10)))+cast(MnyVal as varchar(10))
from TblOne
Assuming that MnyVal is type money and that the varchar length is adequate.
If MnyVal was already a char of some type then the statement becomes the even simplier:
select replicate('0',@MaxLen-len(MnyVal))+MnyVal
from TblOne
Good hunting
August 9, 2006 at 11:36 am
Just a note about building CLR functions. T-SQL is still the native language of SQL Server and processing T-SQL functions is a lot quicker than CLR functions as SQL Server doesn't have to outsource the processing of the function to the CLR engine. CLR isn't meant as a replacement for T-SQL. It's meant as a supplement. T-SQL should be your first option in cases where it could be done in both.
August 9, 2006 at 2:58 pm
Are you sure your solution is simpler than mine?
Can you make the length of returned string to be changed from 10 to, say, 8 without rewriting script, just by supplying a parameter?
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply