如何将同一数据的多种变体转换为一种格式?

xpcnnkqh  于 2022-10-31  发布在  其他
关注(0)|答案(1)|浏览(168)

我对需要从中提取数据的数据库具有只读访问权限。
其中一列是社会安全号码(美国)。数据格式不正确,我正在处理如下条目:

  • 123-45-0000
  • 123 45万
  • 123450000
  • 123-45万美元
  • 123 45-0000电话号码
  • 123,45-0000
  • 123-450000
  • 12345-0000
  • 123,45,0000
  • 123,45,0000,

我们的系统需要SSN如下所示:123-45-0000
我能够弄清楚如何正确地格式化第三个(123450000)与此:

DECLARE @ssn CHAR(12) = 123450000
SELECT stuff(stuff(@ssn, 4, 0, '-'), 7, 0, '-') AS Social_Security_Number

但是,有没有办法把所有这些其他的适合到适当的格式?

yhxst69z

yhxst69z1#

不要再担心哪些符合格式标准,哪些接近,以及它们有多少种不同的错误。只需完全去除格式,只存储数字;将格式应用到其他地方。约束也容易得多。

-- numeric only:

SELECT 
  REPLACE(REPLACE(REPLACE(garbage,'-',' '),',',' '),' ','')
FROM dbo.SSNs;

-- with formatting added back:

SELECT STUFF(STUFF(
  REPLACE(REPLACE(REPLACE(garbage,'-',' '),',',' '),' ',''),
  4,0,'-'),7,0,'-')
FROM dbo.SSNs;

工作示例in this fiddle
修复了现有数据之后,添加一个类似LEN(SSN) = 9 AND SSN NOT LIKE '%[^0-9]%'的约束--在输入过程中从输入中剥离任何非数字,使任何不执行此操作的写入失败,并在UI中(或者在视图或计算列中,如果它不属于该列,则应用破折号)应用破折号(仅用于表示)。

相关问题