主页 / dba / 问题 / 137909
Accepted
Geoff Dawdy
Asked: 2016-05-09 07:59:34 +0800 CST

处理混合 varchar 和 float 数据类型的 sql 列

  • 772

我们有一些 Excel 电子表格，其中包含属性的宗地编号，这些属性应采用标准 varchar 格式，类似于：

[Parcel # 1]          [Building Address]
038-60-171-16-000     1750 W 4th St
038-60-238-09-000     2003-2027 W 4th St
01-15393              201 5th St NE
01-13504              101 5th St SE
25-20-00-03-008.501   1800 E 5th St
36-102-0700-000       2932 S 5th St
280615                2600-B 6th St SW
31-0101.000           1202 16th St
054-259620-00.000     843 N 21st St

不幸的是，我们有一些包裹编号已转换为科学计数法。这意味着它们的包裹编号格式错误，应转换回常规的十进制或数字格式。我在转换那些具有科学记数法的值时遇到了困难。该列是 nvarchar(255) 数据类型，但需要将这些值操作回数字或十进制数据类型。

Parcel Number 1(Min)    Building Address
3.4007000221e+015   247 S Burnett Rd
3.1844401004e+013   433 N Cleveland Ave
3.4007000222e+016   2415 Lexington Ave
E020-0010-006-0211  7271 N Main St
3.6360705006e+013   825 W Market St
3.4007000353e+015   148 W North St
1.40109139e+011     4453-4461 SR-159
16-B-029-E-00-004   7314-7352 Industrial Park Blvd

使用以下 SELECT 语句检索上表

SELECT [Parcel Number 1] 
,[Building Address]
FROM XLSimport
WHERE [Parcel Number 1] like '%e%'

我的理解是，当它采用科学计数法时，我应该能够将值转换为小数。但是，其他具有合法“E”的包裹号码需要保持不变。如何解释不同的“e”值，并且仍将科学计数法的包裹编号转换回其标准包裹编号格式？

我经常遇到将 nvarchar 转换为数字错误消息的错误，我想知道这是否是因为包裹号有 E 但不是科学格式。

例如我运行这个查询：

SELECT
CASE 
    WHEN [Parcel Number 1] like '%E+%' THEN CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL)
    ELSE [Parcel Number 1]
END
FROM XLSimport

然后我收到以下错误：

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
sql-server sql-server-2012

1 个回答

  1. Best Answer

    CASE 的输出只能是一种数据类型。第一个分支是decimal，第二个是nvarchar(255)。

    根据 SQL Server 中的数据类型优先级 (Transact-SQL)规则，CASE 的结果数据类型为decimal.

    nvarchar(255)在 CASE 的第一个分支中再添加一个演员表。

    SELECT
CASE 
    WHEN [Parcel Number 1] like '%E+%'
    THEN CAST(CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
    ELSE [Parcel Number 1]
END
FROM XLSimport
    • 4

相关问题