sql - Force computed column to int

I have this table created with [TankNumId] as a computed column.

How can I make the data type be forced to int. It keeps setting as nvarchar, which is the data type of the column [TankNum].

The values in [TankNum] column are 100-1, 100-2 100-3, etc. Hence why I am using the computed column to convert the '-' to '.' so that it can be a valid int in the [TankNumId] column. Any suggestions?

CREATE TABLE [dbo].[tblTank9](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TankNumId]  AS (replace([TankNum],'-','.' )) PERSISTED NOT NULL,
[TankNum] [nvarchar](10) NULL,
[CompanyName] [nvarchar](30) NULL,
[Ft] [float] NULL,
[Inch] [float] NULL,
[HFt] [smallint] NULL,
[HIn] [smallint] NULL,
[HFx] [smallint] NULL,
[GaPt] [char](100) NULL,
 CONSTRAINT [PK_tblTank9] PRIMARY KEY CLUSTERED 
(
[TankNumId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

1 Answer

  1. Kenny- Reply

    2019-11-15

    replace function receive nvarchar and return nvarchar

    You need CAST or CONVERT your result to float because 100.1 isnt integer

    SELECT CAST(YourVarcharCol AS float) FROM Table
    SELECT CONVERT(float, YourVarcharCol) FROM Table
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>