problem Converting decimal to varchar

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 2005 Forums

Transact-SQL (2005)

problem Converting decimal to varchar

Author

Topic

satish15385
Starting Member

17 Posts

Posted - 2011-11-10 : 13:48:10

I am trying to import data from excel to SQl Server.
I have a column with values "00.12", "00.01" etc...Format.

In Sql Server that column is varchar. how can i replace the decimals here and make the output column as 0012,0001,1302,1500 format

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-10 : 13:49:44

update tbl
set col = replace(replace(col,'.',''),'"')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-10 : 13:50:10

Using the replace funciton?

DECLARE @Foo DECIMAL(14,3) = 987654.123;

SELECT REPLACE(CAST(@Foo AS VARCHAR(15)), '.', '')

satish15385
Starting Member

17 Posts

Posted - 2011-11-10 : 13:53:24

Thank for the Quick response,

I am writting a SSIS Package and so after extracting from excel source, the column values in sql are showing up as follows:
1
1.01
1.09000000
1.10000000
1.11000000
1.12000000
11
11.01
11.07
13
13.01
13.0299999
13.0399999
13.09
13.1
13.1099999
13.1199999

please let me know wat i need to do to get the same format from excel to SQL

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-10 : 13:54:58

Easiest to just run the update after the import. Otherwise it's a datatype conversion and maybe expression in the dataflow.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-10 : 13:56:07

SSIS has a replace function/expression also:
http://msdn.microsoft.com/en-us/library/ms141196.aspx

satish15385
Starting Member

17 Posts

Posted - 2011-11-10 : 15:13:30

Problem i am facing is that...I cannot export excel row "01.00" as same in SQl...its showing as only 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources