SQLServerでゼロサプレス(前０削除)をしたい

Question

SQL Server のクエリ文でデータを抽出したいのですが、
テーブルに格納されたデータの先頭にゼロが付加されているため、
文字列の前ゼロを削除した値を取得したいと思っています。

実際には以下のようなデータが格納されています。

変換前のカラム値
ID

00000
00123
000土地
Null
‘’(空文字)

↓↓↓

変換したい値
ID

0
123
土地
Null
‘’(空文字)

期待したデータを抽出するSQLを教え頂けるでしょうか？

DB環境の関係で
ストアドファンクションを作成したものを使用できず、
困っています。

何卒、よろしくお願いいたします。

Answer 1

「000土地」が無ければいけるんですけどね…
と思ったけど「00123.000」も後ろの０が取れちゃいますね

【追記】
ちょっと思いついたのですごく泥臭い方法も書いてみました。

WITH REC AS (
   SELECT '00000' AS Val UNION ALL
   SELECT '00123' AS Val UNION ALL
   SELECT '00123.000' AS Val UNION ALL
   SELECT '000土地' AS Val UNION ALL
   SELECT NULL AS Val UNION ALL
   SELECT ' ' AS Val
)
SELECT Val

-- 一旦数値に変換する方法。これだと数字以外が混ざっている場合にNG。また小数点以下が落ちる等の問題もある
, CASE ISNUMERIC(Val) WHEN 1 THEN CONVERT(nvarchar, CONVERT(float, Val)) ELSE Val END

-- 0 の数がある程度決まっているなら泥臭い方法でいけなくも、ない…？
-- 先頭7桁がALL0なら8桁目以降を採用、的なやつ 想定される 0 の数だけ WHEN を用意
, CASE
  WHEN LEN(Val) > 7 AND LEFT(Val, 7) = '0000000' THEN SUBSTRING(Val, 8, LEN(Val))
  WHEN LEN(Val) > 6 AND LEFT(Val, 6) = '000000' THEN SUBSTRING(Val, 7, LEN(Val))
  WHEN LEN(Val) > 5 AND LEFT(Val, 5) = '00000' THEN SUBSTRING(Val, 6, LEN(Val))
  WHEN LEN(Val) > 4 AND LEFT(Val, 4) = '0000' THEN SUBSTRING(Val, 5, LEN(Val))
  WHEN LEN(Val) > 3 AND LEFT(Val, 3) = '000' THEN SUBSTRING(Val, 4, LEN(Val))
  WHEN LEN(Val) > 2 AND LEFT(Val, 2) = '00' THEN SUBSTRING(Val, 3, LEN(Val))
  WHEN LEN(Val) > 1 AND LEFT(Val, 1) = '0' THEN SUBSTRING(Val, 2, LEN(Val))
  ELSE Val
  END

FROM REC

Answer 2

試せてないけど、こんな感じではどうでしょうか。

全て'0' なら'0'、それ以外は'0'を空文字に置換する。

case when replicate('0',len(ID))=ID then '0' else replace(ID,'0','') end 

もし、空白が含まれているとしたら、trim()を使用

case when replicate('0',len(ltrim(rtrim(ID))))=ltrim(rtrim(ID)) then '0' else replace(ltrim(rtrim(ID)),'0','') end 

修正

数値と空白を全て空文字にして、結果が''なら全て数字、
・数値に変換して文字に変換（0サプレス）
それ以外は数字と空白を除去
前回同様に試せていません。

case when translate(ID,'0123456789 ','')='' then 
  convert(varchar, convert(int, ID)) 
else 
  translate(ID,'0123456789 ','') 
end

Comments

[alg]

これだと、前ゼロだけでなく後ゼロも削除されてしまいませんか？

[shun1]

そうですね。すべての０が削除されてしまうようです。
例）
120000 =>12
100007 =>17
000001 =>1

[sazi]

algさんの、
>0を半角スペースに置換してから ltrim して、さらに半角スペースを0に置換する
というのはそういう事ですね。
出直しですね。

Answer 3

0を半角スペースに置換してから ltrim して、さらに半角スペースを0に置換する、とかですかね？
元の値に半角スペースが入っていない、という前提付きになってしまいますが。

00000は0にしたいようなので、上記の結果が空文字になったら0にする、というのも追加すれば、たぶんお望みの結果が得られるのではないでしょうか。

※元の値が空文字だった場合はどうしたいのでしょうか？

---

(11/16追記)

ベタに書けば、こんな感じでしょうか。

case
    when ID='' then ''
    when ID is null then null
    when replicate('0',len(ID))=ID then '0'
    else replace(ltrim(replace(ID,'0',' ')),' ','0')
end

動作確認:
sqlfiddleでの実行結果

Comments

[shun1]

回答ありがとうございます。
> ※元の値が空文字だった場合はどうしたいのでしょうか？
元の値がNULL及び空文字の場合もあり、その場合はデータ編集しないため、
NULL及び空文字のままである必要があります。
※半角スペースはデータに含まれていません。
そのため、0を半角スペースに置換してから ltrim して、さらに半角スペースを0に置換すると、
空文字が0になってしまうため、困っているところです。

よろしくお願いいたします。

[alg]

そういった内容は、質問文にも反映したほうがいいですよ。

[shun1]

ご指摘感謝です。
例を更新させていただきました。