1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

データに空白やタブが入っていた時に除去するSQL

Posted at

SQL

UPDATE テーブル名
SET 修正したいカラム名 = TRIM(REPLACE(修正したいカラム名,CHAR(9),'')) --CHAR(9)はTAB
FROM テーブル名

TRIMで空白除去、REPLACEとCHAR(9)の組み合わせでTAB退治。

ホントはデータベースに入ってくる前にアプリケーション側で除去してるはずなのですが。
どこかで漏れていたのでしょう。

ちなみに

CHAR は整数を引数として受け取り、対応する ASCII 文字を返してくれる組み込み関数です。
CHAR(9) - TAB
CHAR(10) - ラインフィード (LF)
CHAR(13) - キャリッジリターン (CR)

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?