CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlfloating-pointvarchar
Jader Dias
Jader Dias

Reputation: 90535

How to insert a float number with quotes into a numeric field?

I have a lengthy script that tries to insert float values with quotes (ie. '15.6') in float fields using T-SQL.

I have reasons to believe this script works in its developer machine, but because of the quotes it fails on my SQL Server 2008.

Is there any way to configure my server to ignore those quotes when the column is numeric? That would be much easier than editing the script.

Upvotes: 5

Views: 63381

Answers (4)

Jader Dias
Jader Dias

Reputation: 90535

It happens that the quoted values are automatically converted to numbers if required.

I didn't happened to me before because my machine was configured with a different decimal separator than the developers machine.

Now that I changed the Regional settings in the control panel, everything works.

Upvotes: 2

ChrisLively
ChrisLively

Reputation: 88062

I would suggest that you first check the revision levels between the two sql servers. The following worked just fine on two I tried; versions 10.0.2746 and 10.0.1600

/****** Object:  Table [dbo].[TestTable]    Script Date: 11/03/2010 14:48:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
    [test] [float] NOT NULL
) ON [PRIMARY]


GO

insert into TestTable(test) values ('15.6')

Upvotes: 1

JNK
JNK

Reputation: 65187

As gbn said, SQL Server sees anything in single quotes as a string.

An alternative (which will still involve changing the script but may be easier than removing the quotes) would be to perform a cast on it:

CAST('12.5' AS FLOAT)

...will return a float value you can use as you like.

Upvotes: 1

gbn
gbn

Reputation: 432421

No, the quotes means it isn't numeric: you'd have to change the script.

How does SQL Server know you really mean float?

I also doubt that it works on another SQL Server instance too.

Upvotes: 1

Related Questions