レコードの特定のカラムに制御文字が含まれている可能性があるため、制御文字が含まれているレコードを抽出する方法を調べる機会がありました。ちょっくら調べてみたので覚書を記載します。制御文字が含まれているレコードを集中する方法を調べる際に下記URLを参考にさせてもらいました。

Remove non printable characters
http://www.sqlservercentral.com/Forums/Topic860321-338-1.aspx

1. 特定のカラムに制御文字が含まれているレコードを抽出するクエリ

下記クエリになります。文字列として特に入っていても問題ないと思われる文字 (水平タブ、LF, CR,スペース) は対象外とするようにしています。CHAR ではなく NCHAR 関数の方がよかった気がしますがとりあえず動いているの文字列結合時点でうまく変換してくれているものだと思います。各文字コードはASCIIコードを検索して調べてください。

DECLARE @pattern nvarchar(37)

-- 下記非表示文字は許可
-- CHAR(9) 水平タブ
-- CHAR(10) LF
-- CHAR(13) CR
-- CHAR(32) スペース

SET @pattern = '%[' 
             + CHAR(0)  + CHAR(1)  + CHAR(2)  + CHAR(3) + CHAR(4)
             + CHAR(5)  + CHAR(6)  + CHAR(7)  + CHAR(8) 
             + CHAR(9)  + CHAR(11) + CHAR(12)
             + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17)
             + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21)
             + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25)
             + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29)
             + CHAR(30) + CHAR(31) + CHAR(127)
             + ']%'

SELECT *
  FROM [TestDB].[dbo].[TestTbl] 
 WHERE PATINDEX(@pattern, UserNameKana) > 0

PATINDEX 関数がワイルドカードを指定できます。ワイルドカードは次のURL参照

 

上記サンプルを参考に 比較をするカラムを適宜変更すれば 制御文字を含むレコードを抽出できます。

2. 制御文字データを空文字に置換する

続いて、制御文字を空文字に置換するサンプルを記載します。空文字ではなくSTUFF関数を使用するので実際には任意の文字列に変更できます。サンプルスクリプトを掲載します。

DECLARE @pattern nvarchar(37)

-- 下記非表示文字は許可
-- CHAR(9) 水平タブ
-- CHAR(10) LF
-- CHAR(13) CR
-- CHAR(32) スペース

SET @pattern = '%[' 
             + CHAR(0)  + CHAR(1)  + CHAR(2)  + CHAR(3) + CHAR(4)
             + CHAR(5)  + CHAR(6)  + CHAR(7)  + CHAR(8) 
             + CHAR(9)  + CHAR(11) + CHAR(12)
             + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17)
             + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21)
             + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25)
             + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29)
             + CHAR(30) + CHAR(31) + CHAR(127)
             + ']%'

UPDATE [TestDB].[dbo].[TestTbl]
   set UserNameKana = STUFF(UserNameKana, PATINDEX(@pattern, UserNameKana), 1, '')
 WHERE PATINDEX(@pattern, UserNameKana) > 0

サンプルクエリでは、空文字をSTUFF関数で設定していますが、 任意の文字列を指定することができます。

3.まとめ

制御文字がそもそも含まれていることがそもそもおかしい気がしますが、また今後使うことがあるかわかりませんが、制御文字が含まれているレコード抽出するクエリの説明は以上です。間違い、指摘点などがありまｓたらご連絡ください。