How do i conver from varchar to float

What does your varchar value look like ?


declare @v varchar(10)
set @v = '123.4324'

select CAST(@v as float)

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I have brought the data in from a csv file. Converted the colums to varchar 12.
Now i am trying to convert the columns so the that they are numeric. decimal so that i can calulate fields.
These fields will be used within a cube.
the data looks like the following.
Labour Hours varchar 12 and sample data looks like 0.10
rate varchar 12 and sample data looks like 24.30
amount varchar 12 and sample data looks like 2.51

I keep getting a error when trying to convert.
I have tried declaring a variable but this failed.


----- Allan Mitchell wrote: -----

What does your varchar value look like ?


declare @v varchar(10)
set @v = '123.4324'

select CAST(@v as float)

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

OK can you adapt this ?

CREATE TABLE IHaveNumerics (Col1 varchar(12))
INSERT IHaveNumerics VALUES('0.10')

select
col1,
CAST(col1 as decimal(5,2)) as DecimalVersion,
CAST(col1 as Numeric(5,2)) as NumericVersion
from
IHaveNumerics




--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I created the table like you said and did a insert into.
I then tried to convert data but it failed.
What other steps can i use to clean this data and then convert to the required format.

Can you show me your exact steps?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I will start from the beginning , i import data into a table from a csv file.
From there i have convert the data to varchar 12 and imported into stg table 2 from here i am trying to convert the data into numeric or decimal using a view and the convert. But whenever i try to convert from varchar the convert fails.

Show me a varchar(12) value that fails
show me the CONVERT statement
Show me the error/output
Show me the view.
Why is your table's column definition not a Numeric from the start. SQL
Server is prettty good at Implicitly converting values.

I.e.

1.234
CAST(col1 as Decimal(5,2)
"Invalid character for CAST ............"
CREATE VIEW.................

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

using a view and the convert. But whenever i try to convert from varchar the
convert fails.

Allen thanks for your help with this 1. I have located the problem. It starts at the source. Some of the fields have spaces in the cells. This is causing the error when trying to convert. I will have to construct the source data correctly remove all spaces from the cells and then sql will be able to convert to the format that i require...

cheers,

Ahhh good so bad data is the issue.

What is the Source, a text file right.?

When you get it into SQL Server you could do

REPLACE(col, ' ','') and see if that gets rid of everything for you.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"shane" <anonymous (AT) discussions (DOT) microsoft.com> wrote

causing the error when trying to convert. I will have to construct the
source data correctly remove all spaces from the cells and then sql will be
able to convert to the format that i require...