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