回答編集履歴
4
CASE文を少し短くしました
answer
CHANGED
|
@@ -21,20 +21,13 @@
|
|
|
21
21
|
-- 0 の数がある程度決まっているなら泥臭い方法でいけなくも、ない…？
|
|
22
22
|
-- 先頭7桁がALL0なら8桁目以降を採用、的なやつ 想定される 0 の数だけ WHEN を用意
|
|
23
23
|
, CASE
|
|
24
|
-
WHEN Val = '0000000' THEN '0'
|
|
25
|
-
WHEN Val = '000000' THEN '0'
|
|
26
|
-
WHEN Val = '00000' THEN '0'
|
|
27
|
-
WHEN Val = '0000' THEN '0'
|
|
28
|
-
WHEN Val = '000' THEN '0'
|
|
29
|
-
WHEN Val = '00' THEN '0'
|
|
30
|
-
WHEN Val = '0' THEN '0'
|
|
31
|
-
WHEN LEFT(Val, 7) = '0000000' THEN SUBSTRING(Val, 8, LEN(Val))
|
|
24
|
+
WHEN LEN(Val) > 7 AND LEFT(Val, 7) = '0000000' THEN SUBSTRING(Val, 8, LEN(Val))
|
|
32
|
-
WHEN LEFT(Val, 6) = '000000' THEN SUBSTRING(Val, 7, LEN(Val))
|
|
25
|
+
WHEN LEN(Val) > 6 AND LEFT(Val, 6) = '000000' THEN SUBSTRING(Val, 7, LEN(Val))
|
|
33
|
-
WHEN LEFT(Val, 5) = '00000' THEN SUBSTRING(Val, 6, LEN(Val))
|
|
26
|
+
WHEN LEN(Val) > 5 AND LEFT(Val, 5) = '00000' THEN SUBSTRING(Val, 6, LEN(Val))
|
|
34
|
-
WHEN LEFT(Val, 4) = '0000' THEN SUBSTRING(Val, 5, LEN(Val))
|
|
27
|
+
WHEN LEN(Val) > 4 AND LEFT(Val, 4) = '0000' THEN SUBSTRING(Val, 5, LEN(Val))
|
|
35
|
-
WHEN LEFT(Val, 3) = '000' THEN SUBSTRING(Val, 4, LEN(Val))
|
|
28
|
+
WHEN LEN(Val) > 3 AND LEFT(Val, 3) = '000' THEN SUBSTRING(Val, 4, LEN(Val))
|
|
36
|
-
WHEN LEFT(Val, 2) = '00' THEN SUBSTRING(Val, 3, LEN(Val))
|
|
29
|
+
WHEN LEN(Val) > 2 AND LEFT(Val, 2) = '00' THEN SUBSTRING(Val, 3, LEN(Val))
|
|
37
|
-
WHEN LEFT(Val, 1) = '0' THEN SUBSTRING(Val, 2, LEN(Val))
|
|
30
|
+
WHEN LEN(Val) > 1 AND LEFT(Val, 1) = '0' THEN SUBSTRING(Val, 2, LEN(Val))
|
|
38
31
|
ELSE Val
|
|
39
32
|
END
|
|
40
33
|
|
3
不要なコメントを削除
answer
CHANGED
|
@@ -6,12 +6,12 @@
|
|
|
6
6
|
|
|
7
7
|
```sql
|
|
8
8
|
WITH REC AS (
|
|
9
|
-
SELECT '00000' AS Val UNION ALL
|
|
9
|
+
SELECT '00000' AS Val UNION ALL
|
|
10
|
-
SELECT '00123' AS Val UNION ALL
|
|
10
|
+
SELECT '00123' AS Val UNION ALL
|
|
11
|
-
SELECT '00123.000' AS Val UNION ALL
|
|
11
|
+
SELECT '00123.000' AS Val UNION ALL
|
|
12
|
-
SELECT '000土地' AS Val UNION ALL
|
|
12
|
+
SELECT '000土地' AS Val UNION ALL
|
|
13
|
-
SELECT NULL AS Val UNION ALL
|
|
13
|
+
SELECT NULL AS Val UNION ALL
|
|
14
|
-
SELECT ' ' AS Val
|
|
14
|
+
SELECT ' ' AS Val
|
|
15
15
|
)
|
|
16
16
|
SELECT Val
|
|
17
17
|
|
2
SUBSTRINGよりLEFTの方が見やすいかも
answer
CHANGED
|
@@ -28,13 +28,13 @@
|
|
|
28
28
|
WHEN Val = '000' THEN '0'
|
|
29
29
|
WHEN Val = '00' THEN '0'
|
|
30
30
|
WHEN Val = '0' THEN '0'
|
|
31
|
-
WHEN
|
|
31
|
+
WHEN LEFT(Val, 7) = '0000000' THEN SUBSTRING(Val, 8, LEN(Val))
|
|
32
|
-
WHEN
|
|
32
|
+
WHEN LEFT(Val, 6) = '000000' THEN SUBSTRING(Val, 7, LEN(Val))
|
|
33
|
-
WHEN
|
|
33
|
+
WHEN LEFT(Val, 5) = '00000' THEN SUBSTRING(Val, 6, LEN(Val))
|
|
34
|
-
WHEN
|
|
34
|
+
WHEN LEFT(Val, 4) = '0000' THEN SUBSTRING(Val, 5, LEN(Val))
|
|
35
|
-
WHEN
|
|
35
|
+
WHEN LEFT(Val, 3) = '000' THEN SUBSTRING(Val, 4, LEN(Val))
|
|
36
|
-
WHEN
|
|
36
|
+
WHEN LEFT(Val, 2) = '00' THEN SUBSTRING(Val, 3, LEN(Val))
|
|
37
|
-
WHEN
|
|
37
|
+
WHEN LEFT(Val, 1) = '0' THEN SUBSTRING(Val, 2, LEN(Val))
|
|
38
38
|
ELSE Val
|
|
39
39
|
END
|
|
40
40
|
|
1
2つめの案を追加
answer
CHANGED
|
@@ -1,6 +1,9 @@
|
|
|
1
1
|
「000土地」が無ければいけるんですけどね…
|
|
2
2
|
と思ったけど「00123.000」も後ろの０が取れちゃいますね
|
|
3
3
|
|
|
4
|
+
【追記】
|
|
5
|
+
ちょっと思いついたのですごく泥臭い方法も書いてみました。
|
|
6
|
+
|
|
4
7
|
```sql
|
|
5
8
|
WITH REC AS (
|
|
6
9
|
SELECT '00000' AS Val UNION ALL -- OK
|
|
@@ -11,7 +14,30 @@
|
|
|
11
14
|
SELECT ' ' AS Val -- OK
|
|
12
15
|
)
|
|
13
16
|
SELECT Val
|
|
17
|
+
|
|
18
|
+
-- 一旦数値に変換する方法。これだと数字以外が混ざっている場合にNG。また小数点以下が落ちる等の問題もある
|
|
14
19
|
, CASE ISNUMERIC(Val) WHEN 1 THEN CONVERT(nvarchar, CONVERT(float, Val)) ELSE Val END
|
|
20
|
+
|
|
21
|
+
-- 0 の数がある程度決まっているなら泥臭い方法でいけなくも、ない…？
|
|
22
|
+
-- 先頭7桁がALL0なら8桁目以降を採用、的なやつ 想定される 0 の数だけ WHEN を用意
|
|
23
|
+
, CASE
|
|
24
|
+
WHEN Val = '0000000' THEN '0'
|
|
25
|
+
WHEN Val = '000000' THEN '0'
|
|
26
|
+
WHEN Val = '00000' THEN '0'
|
|
27
|
+
WHEN Val = '0000' THEN '0'
|
|
28
|
+
WHEN Val = '000' THEN '0'
|
|
29
|
+
WHEN Val = '00' THEN '0'
|
|
30
|
+
WHEN Val = '0' THEN '0'
|
|
31
|
+
WHEN SUBSTRING(Val, 1, 7) = '0000000' THEN SUBSTRING(Val, 8, LEN(Val))
|
|
32
|
+
WHEN SUBSTRING(Val, 1, 6) = '000000' THEN SUBSTRING(Val, 7, LEN(Val))
|
|
33
|
+
WHEN SUBSTRING(Val, 1, 5) = '00000' THEN SUBSTRING(Val, 6, LEN(Val))
|
|
34
|
+
WHEN SUBSTRING(Val, 1, 4) = '0000' THEN SUBSTRING(Val, 5, LEN(Val))
|
|
35
|
+
WHEN SUBSTRING(Val, 1, 3) = '000' THEN SUBSTRING(Val, 4, LEN(Val))
|
|
36
|
+
WHEN SUBSTRING(Val, 1, 2) = '00' THEN SUBSTRING(Val, 3, LEN(Val))
|
|
37
|
+
WHEN SUBSTRING(Val, 1, 1) = '0' THEN SUBSTRING(Val, 2, LEN(Val))
|
|
38
|
+
ELSE Val
|
|
39
|
+
END
|
|
40
|
+
|
|
15
41
|
FROM REC
|
|
16
42
|
|
|
17
43
|
```
|