替换数据库中SSM的SQL语句

发布于 2024-12-09 11:19:34 字数 189 浏览 1 评论 0原文

我将如何创建一个查询来替换文本块中间的社会安全号码？

表格是这样的：

column1     column2
11          text SSN more text

SSN 的格式也不尽相同，有些是这样的 xxx-xx-xxxx，有些只是一个数字字符串 xxxxxxxx。

原文

How would I go about creating a query to replace a social security number from the middle of a text block?

The table is like this:

column1     column2
11          text SSN more text

SSNs are not all formatted the same either, some are like this xxx-xx-xxxx and some are just a number string xxxxxxxx.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

埋情葬爱 2024-12-16 11:19:34

例如，如果您想从表中编辑 SSN#，则可以使用 PATINDEX 函数查找 SSN# 和 REPLACE 命令将它们转换为隐藏数字。下面是一个示例（请注意，此代码假设一个值中一次只有一个 SSN#。如果可能有多个不同的 SSN#，则只会找到第一个）：

update
    MyTable
set
    Column2 = replace(Column2, 
        case
            when patindex('% [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then substring(Column2, patindex('% [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] %', Column2) + 1, 11)
            when patindex('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then substring(Column2, patindex('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %', Column2) + 1, 9)
            else ''
        end,
        case
            when patindex('% [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then 'xxx-xx-xxxx'
            when patindex('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then 'xxxxxxxxx'
            else ''
        end)

示例输入数据：

1 | text 123-45-6789 more text
2 | testing 894315466 and blah blah blah
3 | testing no ssn 348933 result

示例输出：

1 | text xxx-xx-xxxx more text
2 | testing xxxxxxxxx and blah blah blah
3 | testing no ssn 348933 result

If you are wanting for example to redact SSN#'s from your table, you can make use of the PATINDEX function to find SSN#'s and the REPLACE command to convert them to something that hides the number. Here is an example (note that this code assumes there will only be one SSN# in a value at once. If there could be multiple different SSN#'s, only the first one will be found):

update
    MyTable
set
    Column2 = replace(Column2, 
        case
            when patindex('% [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then substring(Column2, patindex('% [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] %', Column2) + 1, 11)
            when patindex('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then substring(Column2, patindex('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %', Column2) + 1, 9)
            else ''
        end,
        case
            when patindex('% [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then 'xxx-xx-xxxx'
            when patindex('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %', Column2) <> 0 
                then 'xxxxxxxxx'
            else ''
        end)

Sample input data:

1 | text 123-45-6789 more text
2 | testing 894315466 and blah blah blah
3 | testing no ssn 348933 result

Sample Output:

1 | text xxx-xx-xxxx more text
2 | testing xxxxxxxxx and blah blah blah
3 | testing no ssn 348933 result

回复收藏 0 原文

东京女 2024-12-16 11:19:34

根据您字段中的文本，您可以替换所有连字符，假设您正在尝试将 SSN 标准化为无连字符的格式。但是，如果嵌套 SSN 的其他文本可能包含连字符，则以下解决方案将不起作用。它最终会拉出所有的连字符。

UPDATE tableA
SET column2 = REPLACE(column2,'-','');

否则，您需要根据一行中的 9 个数字或 3 个数字，后跟一个连字符，然后是 2 个数字，一个连字符，然后是 4 个数字来查找 SSN。这将需要您浏览整个字段，标记您的起点以开始替换并进行解析工作。

Depending on the text you have in your field, you could do a replace on all of the hypens, assuming you are trying to normalize the SSNs in to a hypen-less format. However, if your other text that the SSN is nested in may contain hypens, the below solution won't work. It'll end up pulling out all of the hypens.

UPDATE tableA
SET column2 = REPLACE(column2,'-','');

Otherwise, you're dealing with finding the SSN based off 9 numerical digits in a row or 3 digits, followed by a hypen, followed by 2 digits, a hypen, then four. This would require you to go through the entire field, mark your starting point to start the replacement and doing your parsing work.

回复收藏 0 原文

~没有更多了~