The data format problem(urgent!)...

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

The data format problem(urgent!)...

Author

Topic

yeel2000
Starting Member

3 Posts

Posted - 2002-05-04 : 23:50:48

How can I export data in SQL server tables to text file with the
following format:

SQL table =====> text file(fixed length 10)
12345.67 =====> 0001234567

pendding zeros and omit decimal point.
Any one can give me some hints or directions of solutions?

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-05-05 : 02:30:00

The following select statement will format it the way you described.
Customize the following to your specific needs and then use it from osql or DTS to export to text.

declare @decimal decimal(18,2)
select @decimal = 12345.67

select right(convert(varchar(11),10000000000 + convert(int,replace(convert(varchar(10),@decimal),'.',''))),10)

yeel2000
Starting Member

3 Posts

Posted - 2002-05-05 : 11:45:35

Thanks for your help but I think you are somewhat misunderstanding..
actually I want to transfer SQL table decimal type collumn data
into fixed length chars, with pedding 0.
So the data maybe 12345.67, maybe 123.45 and so on.
can you give me some advise?

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-05-05 : 13:26:10

quote:

can you give me some advise?

Did you try what I sent you?

Just customize it to the table you are transforming:

select right(convert(varchar(11),10000000000 + convert(int,replace(convert(varchar(10),<some_decimal_column>),'.',''))),10)
from <some_table>

If the formula is incorrect you need to send more examples and information so myself or others can help.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2002-05-05 : 18:39:18

jbkayne!

Can you explain how does this select statement works?

thanks

yeel2000
Starting Member

3 Posts

Posted - 2002-05-05 : 21:37:19

Now my problem is solved.
I just use T-SQL statement in jobs to copy the table data to
another table with the desire format(varchar) using "left" and
"convert" to doing it.
But if the table is very large and this kind of task is scheduled
every day, quite a lot of time is consumed.
Is there any other solutions to it with more efficiency ? :-)

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-05-05 : 23:12:44

quote:

jbkayne!

Can you explain how does this select statement works?

thanks

The trick to this algorithm is the following:

Since there is a fixed width of 10, I simply add 10000000000
to the number and then take the right 10 characters. This way I can easily pad the number with zeros without any loops or case logic. I have also seen people use the REPLICATE function to solve this one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources