如何在SQL中去除X12字段中的非允许字符？

找不到现成的去除X12字段中非允许字符的方案，特此分享这款自定义SQL函数。它支持根据文件及合作方需求，选择仅保留X12基础允许字符集或扩展允许字符集。

允许字符集明细 #

基础允许字符集 #

• 大写字母：A-Z
• 数字：0-9
• 特殊字符：! " & ' ( ) * + , - . / : ; ? =
• 空格字符
• 控制字符：BEL、HT、LF、VT、FF、CR、FS、GS、RS、US、NL
• ASCII十六进制值：07、09、0A、0B、0C、0D、1C、1D、1E、1F

扩展允许字符集 #

• 小写字母：a-z
• 其他特殊字符：% ~ @ [ ] _ { } \ | < >
• 国家字符：# $
• 控制字符：SOH、STX、ETX、EOT、ENQ、ACK、DC1、DC2、DC3、DC4、NAK、SYN、ETB
• ASCII十六进制值：01、02、03、04、05、06、11、12、13、14、15、16、17

完整函数代码及测试示例 #

-- 此函数用于移除非X12字符，支持选择移除X12扩展字符（%~@[]_{}\|<>"等）
/* 
测试示例：
Declare @text as nvarchar(max) = N'% ~ @ [ ] _ { } \ | < > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z / { } *'  + char(27) + char(189) + char(191) + '* 0 1 2 3 4 5 6 7 8 9 ! " & ( ) * + , - . / : ; ? = _ ^ %'
Print @text
Print [dbo].[f_remove_non_x12_chars_extended](@text,'')
Print [dbo].[f_remove_non_x12_chars_extended](@text,'~@_{}\|<>"')
Print [dbo].[f_remove_non_x12_chars_extended](@text,'[]~@_{}\|<>"')
Print [dbo].[f_remove_non_x12_chars_extended](@text,'[]~@_{}\|<>"%^"')
Print [dbo].[f_remove_non_x12_chars_extended](@text,'[]~@_{}\|<>"%^-"*')
Print [dbo].[f_remove_non_x12_chars_extended](@text,'[]~@_{}\|<>"%^-"* ')
*/
CREATE Function [dbo].[f_remove_non_x12_chars_extended](@text VarChar(MAX), @extraChars varchar(100) = '')
Returns VarChar(1000)
AS
Begin
    -- PATINDEX会将[]%_^-视为通配符，需特殊处理
    Declare @hasOpenBracket as bit = 0
    Declare @hasClosedBracket as bit = 0
    Declare @hasPercent as bit = 0
    Declare @hasUnderscore as bit = 0
    Declare @hasCaret as bit = 0
    Declare @hasDash as bit = 0

    -- 若额外字符集中包含通配符，先移除并标记
    if CHARINDEX('[', @extraChars) > 0  begin set @hasOpenBracket = 1;  set @extraChars = REPLACE(@extraChars, '[', '');    end
    if CHARINDEX(']', @extraChars) > 0  begin set @hasClosedBracket = 1;set @extraChars = REPLACE(@extraChars, ']', '');    end
    if CHARINDEX('%', @extraChars) > 0  begin set @hasPercent = 1;      set @extraChars = REPLACE(@extraChars, '%', '');    end
    if CHARINDEX('_', @extraChars) > 0  begin set @hasUnderscore = 1;   set @extraChars = REPLACE(@extraChars, '_', '');    end
    if CHARINDEX('^', @extraChars) > 0  begin set @hasCaret = 1;        set @extraChars = REPLACE(@extraChars, '^', '');    end
    if CHARINDEX('-', @extraChars) > 0  begin set @hasDash = 1;         set @extraChars = REPLACE(@extraChars, '-', '');    end

    Declare @removeValues as varchar(50) = '%[' + CHAR(0) + '-' + CHAR(31) + CHAR(127) + '-' + CHAR(255)  + @extraChars +  ']%'
    Declare @ptr int = PatIndex(@removeValues, @text COLLATE Latin1_General_100_BIN2)
    While @ptr > 0
    BEGIN
        Set @text = Stuff(@text, @ptr, 1, '')
        Set @ptr  = PatIndex(@removeValues, @text COLLATE Latin1_General_100_BIN2)
    END

    -- 根据标记移除对应通配符
    if @hasOpenBracket  = 1     begin set @text = replace(@text, '[', '') end
    if @hasClosedBracket = 1    begin set @text = replace(@text, ']', '') end
    if @hasPercent  = 1         begin set @text = replace(@text, '%', '') end
    if @hasUnderscore   = 1     begin set @text = replace(@text, '_', '') end
    if @hasCaret    = 1         begin set @text = replace(@text, '^', '') end
    if @hasDash = 1             begin set @text = replace(@text, '-', '') end

    Return @text
    -- Return CONCAT(@hasOpenBracket, @hasClosedBracket, @hasPercent, @hasUnderscore, @hasCaret, @extraChars, @text)
End

欢迎提出合理的改进建议，请勿发表贬低性言论。

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