如何在SQL Server视图中将VarChar类型字段转为int类型

要把视图里的fieldValue字段从VARCHAR类型转为INT类型，直接在SELECT语句中使用CAST()或CONVERT()函数即可，以下是两种实现方式：

方法1：使用CAST函数 #

ALTER VIEW dbo.NpsResultsView
AS
SELECT        
    dbo.CRMDeals.dealid,
    dbo.CRMDeals.contact,
    dbo.CRMContacts.title,
    dbo.CRMContacts.firstname,
    dbo.CRMContacts.lastname, 
    dbo.CRMContacts.dealCustomFieldId,
    CAST(dbo.CRMCustomFieldsData.fieldValue AS INT) AS fieldValue,
    dbo.CRMCustomFieldsData.updatedTimestamp, 
    dbo.ClientsMigrationDataset.Groups AS Expr1, 
    dbo.ClientsMigrationDataset.Client,
    dbo.ClientsMigrationDataset.Email,
    dbo.ClientsMigrationDataset.ClientManager
FROM
    dbo.CRMDeals
INNER JOIN    
    dbo.CRMContacts ON dbo.CRMContacts.contactid = dbo.CRMDeals.contact 
INNER JOIN    
    dbo.CRMDealCustomFieldsData ON dbo.CRMDealCustomFieldsData.dealId = dbo.CRMDeals.dealid 
INNER JOIN 
    dbo.ClientsMigrationDataset ON dbo.ClientsMigrationDataset.Email = dbo.CRMContacts.email
WHERE        
    (dbo.CRMDealCustomFieldsData.dealCustomFieldId = '15')

方法2：使用CONVERT函数 #

ALTER VIEW dbo.NpsResultsView
AS
SELECT        
    dbo.CRMDeals.dealid,
    dbo.CRMDeals.contact,
    dbo.CRMContacts.title,
    dbo.CRMContacts.firstname,
    dbo.CRMContacts.lastname, 
    dbo.CRMContacts.dealCustomFieldId,
    CONVERT(INT, dbo.CRMCustomFieldsData.fieldValue) AS fieldValue,
    dbo.CRMCustomFieldsData.updatedTimestamp, 
    dbo.ClientsMigrationDataset.Groups AS Expr1, 
    dbo.ClientsMigrationDataset.Client,
    dbo.ClientsMigrationDataset.Email,
    dbo.ClientsMigrationDataset.ClientManager
FROM
    dbo.CRMDeals
INNER JOIN    
    dbo.CRMContacts ON dbo.CRMContacts.contactid = dbo.CRMDeals.contact 
INNER JOIN    
    dbo.CRMDealCustomFieldsData ON dbo.CRMDealCustomFieldsData.dealId = dbo.CRMDeals.dealid 
INNER JOIN 
    dbo.ClientsMigrationDataset ON dbo.ClientsMigrationDataset.Email = dbo.CRMContacts.email
WHERE        
    (dbo.CRMDealCustomFieldsData.dealCustomFieldId = '15')

操作步骤（SQL Server Management Studio） #

1. 打开SSMS，连接到目标数据库
2. 在对象资源管理器中找到该视图，右键选择设计，直接在设计界面修改fieldValue的表达式；或者右键选择编写视图脚本为 -> ALTER到 -> 新查询编辑器窗口，将上述修改后的代码粘贴执行
3. 执行ALTER VIEW语句完成修改

注意事项 #

如果fieldValue中存在非数字内容，直接转换会报错。若需要容错处理，可以使用TRY_CAST()或TRY_CONVERT()函数，转换失败时会返回NULL：

TRY_CAST(dbo.CRMCustomFieldsData.fieldValue AS INT) AS fieldValue

内容的提问来源于stack exchange，提问作者Stady