0

Dear SIR,
I am using sqlserver2005.
Table1 has a column named bill_no and its type is text
Now I want to change its type "Numeric"
When I do this the it displays this error message

---------------------------
Microsoft SQL Server Management Studio
---------------------------
Conversion from 'text' to 'numeric' is not supported on the connected database server.

How to change type text to numeric
I do not want it through vb.net codes.
I want to apply it directly in sqlserver 2005.

Please help

3
Contributors
2
Replies
4
Views
7 Years
Discussion Span
Last Post by sknake
0

the text datatype is for BLOBs. You can substring the data out if you inadvertently used a blob for number storage? You can probably do this in fewer operations, but to clearly demonstrate the point:

IF OBJECT_ID('Test123', 'U') IS NOT NULL DROP TABLE Test123
Create Table Test123
(
  ID int,
  Col text
)

Insert Into Test123 (ID, Col) Values (1, '5.5')

GO

Alter Table Test123 Add ColNumber Numeric

GO

Update Test123 Set ColNumber = SUBSTRING(Col, 1, 50) Where IsNumeric(SUBSTRING(Col, 1, 50)) = 1

GO

Alter Table Test123 Drop Column Col

GO

Alter Table Test123 Add Col Numeric

GO

Update Test123 Set Col = ColNumber

GO

Alter Table Test123 Drop Column ColNumber
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.