Most of the case database developer used to load data and download in different formats.
Here's one solution to download our data in a specified length formats.
Create a function.
>
CREATE Function [dbo].[UFN_COLUMNFORMAT]
(@format nVARCHAR(10), @value nVARCHAR(100))
Returns nVarchar(max)
As
Begin
DECLARE @value1 VARCHAR(100), @Fchar VARCHAR(5), @innerchar VARCHAR(5), @symbol VARCHAR(1),
@leninnerchar INT, @FFormat CHAR(20), @m INT, @n INT, @sql NVARCHAR(max),@ValueFordate datetime
SELECT @Fchar = Substring(@format, 1, Charindex('(', @format) - 1)
SELECT @innerchar = Substring(@format, Charindex('(', @format) + 1, ((Charindex(')', @format) - Charindex('(', @format)) - 1))
SELECT @symbol = Substring(@format, Charindex(')', @format) + 1, len(@format))
SELECT @m = CASE
WHEN charindex('#', @innerchar) > 0
THEN substring(@innerchar, 1, charindex('#', @innerchar) - 1)
WHEN charindex(',', @innerchar) > 0
THEN substring(@innerchar, 1, charindex(',', @innerchar) - 1)
ELSE 0
END
SELECT @n = CASE
WHEN charindex('#', @innerchar) > 0
THEN substring(@innerchar, charindex('#', @innerchar) + 1, len(@innerchar))
WHEN charindex(',', @innerchar) > 0
THEN substring(@innerchar, charindex(',', @innerchar) + 1, len(@innerchar))
ELSE @innerchar
END
SELECT @FFormat = CASE
WHEN @Fchar = 'A'
THEN 'A'
WHEN @Fchar = 'A0'
THEN 'A0'
WHEN @Fchar = 'N'
THEN 'N'
WHEN @Fchar = 'Date'
THEN 'Date'
END + '(' + CASE
WHEN charindex('#', @innerchar) > 0
THEN 'm#n'
WHEN charindex(',', @innerchar) > 0
THEN 'm,n'
ELSE 'n'
END + ')' + CASE
WHEN @symbol = ''
THEN ''
ELSE @symbol
END
IF @FFormat='A(n)'
Select @value1= LEFT(CAST( @value AS VARCHAR( max)) + REPLICATE(' ', @n) ,@n)
IF @FFormat='A(n)+'
Select @value1= LEFT(@symbol+CAST( @value AS VARCHAR( max) )+ REPLICATE(' ', @n),@n)
IF @FFormat='A0(n)'
Select @value1= LEFT(CAST( @value AS VARCHAR( max)) +REPLICATE('0', @n),@n)
IF @FFormat='A0(n)+'
Select @value1= @symbol+LEFT( REPLICATE('0', @n-1) +CAST( @value AS VARCHAR( max) ),@n-1)
IF @FFormat='A(m#n)'
Select @value1= LEFT(CAST( replace(@value,'.','') AS VARCHAR( max) )+ REPLICATE(' ',@m),@m)
IF @FFormat='A0(m#n)'
Select @value1= LEFT( CAST( replace(@value,'.','') AS VARCHAR( max) )+REPLICATE('0',@m),@m)
IF @FFormat='A0(m,n)+'
Select @value1= @symbol+ LEFT( REPLICATE('0',@m-@n-1) +CAST( substring(@value,1,CHARINDEX('.',@value)) AS VARCHAR( max) ),@m-@n-1)+ Left( CAST( substring(@value,CHARINDEX('.',@value)+1,len(@value)) AS VARCHAR( max) )+REPLICATE('0', @n) , @n)
IF @FFormat='A0(m,n)'
Select @value1= LEFT( CAST( substring(@value,1,CHARINDEX('.',@value)) AS VARCHAR( max) ) + REPLICATE('0',@m-@n) ,@m-@n)+ Left( CAST( substring(@value,CHARINDEX('.',@value)+1,len(@value)) AS VARCHAR( max) )+REPLICATE('0', @n) , @n)
IF @FFormat='N(m,n)'
Select @value1= LEFT(CAST( @value AS VARCHAR( max) )+REPLICATE('0',@m-1),@m-1)
IF @FFormat='N(n)'
Select @value1= LEFT(CAST( @value AS VARCHAR( max) )+REPLICATE(' ',@n),@n)
IF @FFormat='Date(n)'
select @value1= convert(varchar(50),convert(datetime,@value),112)
IF @FFormat='Date(n)'
select @value1= convert(varchar(50),convert(datetime,@value),20)
Return @value1
End
--
Create a employee table
---
CREATE TABLE [dbo].[employee](
[empid] [int] NULL,
[ename] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]
---
insert data
----
EmpId Ename ESal
1 Raj 20000.00
2 Rajesh 4444.00
3 King 45454.00
1 Raj 4545.00
2 Rajesh 45454.00
4 Hello 4454.00
---
Now My requirement is to format out put data with specific length out put column .
Eg:
EmpId length is : 5
Ename Length is : 50
Salary Length is : 10
--
My query will be like
--
select
dbo.[UFN_COLUMNFORMAT]('A(5)',empid)+
dbo.[UFN_COLUMNFORMAT]('A(50)',ename)+
dbo.[UFN_COLUMNFORMAT]('A(10)',salary)
from employee
1 Raj 20000.00
2 Rajesh 4444.00
3 King 45454.00
1 Raj 4545.00
2 Rajesh 45454.00
4 Hello 4454.00
