代码之家 › 专栏 › 技术社区 › D.R.

标准化不同区域的数字格式

sql-server-2008-r2 tsql validation sql-server

D.R. · 技术社区 · 6 年前

我正在对一个旧系统进行大规模清理,我有一个带有 TEXT -键入存储数字(包括货币)数据的列(以及文本数据),通常采用本地化格式或打字错误。我需要将数据标准化为美国数字标准。

一些数据示例:

$1,000 - Good!
$1.000 - Bad, should have been $1,000
$1,000.000 - Bad, should have been $1,000,000
$1,000.000.00 - Bad, should have been $1,000,000.00
$1.000.000,00 - Bad, should have been $1,000,000.00
$10,.000 - Bad, should have been $10,000
500.000 - Bad, should have been 500,000
1.325% - Good!

我举了一些例子,因为我想说明在发现和纠正问题上的一些困难。我的前提是,一个句点后面跟着3个数字应该是逗号(除非它可能是精确的%而不是美元),但是一个句点后面跟着2个数字是正确的。有人对在SQL中清除这个问题有什么建议吗,或者有更好的现成解决方案吗?

2 回复 | 直到 6 年前

HABO 6 年前

Decimal(16,4)

-- Process "$n.nn".
update MyTable
  set DecimalValue = Cast( TextValue as Decimal(16,4) ),
    Unit = 'USD', Scale = 2 -- If desired.
  where DecimalValue is NULL and TextValue like '$[0-9].[0-9][0-9]';

-- Process "$n.nnn,nn".
update MyTable
  set DecimalValue = Cast( Replace( Replace( TextValue, '.', '' ), ',', '.' ) as Decimal(16,4) )
  where DecimalValue is NULL and TextValue like '$[0-9].[0-9][0-9][0-9],[0-9][0-9]';

-- Process ".nn%", "n.nn%" and "nn.nn%".
update MyTable
  set DecimalValue = Cast( Replace( TextValue, '%', '' ) as Decimal(16,4) ),
    Unit = 'percent', Scale = 2 -- If desired.
  where DecimalValue is NULL and (
    TextValue like '.[0-9][0-9]*%' escape '*' or
    TextValue like '[0-9].[0-9][0-9]*%' escape '*' or
    TextValue like '[0-9][0-9].[0-9][0-9]*%' escape '*' );

where DecimalValue is NULL

S3S 6 年前

declare @table table (ID int identity (1,1), c1 varchar(64))
insert into @table
values
('$1,000'), --good
('$1.000'), -- Bad, should have been $1,000
('$1,000.000'), -- Bad, should have been $1,000,000
('$1,000.000.00'), -- Bad, should have been $1,000,000.00
('$10,.000'), -- Bad, should have been $10,000
('500.000'), -- Bad, should have been 500,000
('1.325%'), -- Good!
('1,325%') -- bad!

select
    *,
    case
        when c1 like '%\%%' escape '\' then replace(c1,',','.') --simply replaces commas with periods for % signed values
        else 
            case                                                --simply replaces periods for commans for non % signed values, and takes into account ,00 at the end should be .00
                                                                --also handles double commas, once
                when left(right(replace(replace(c1,'.',','),',,',','),3),1) = ','
                then stuff(replace(replace(c1,'.',','),',,',','),len(replace(replace(c1,'.',','),',,',',')) - 2,1,'.')
                else replace(replace(c1,'.',','),',,',',')      
            end 
    end
from @table