回答編集履歴
4
誤字
test
CHANGED
|
@@ -30,7 +30,7 @@
|
|
|
30
30
|
|
|
31
31
|
```
|
|
32
32
|
|
|
33
|
-
SELEC
|
|
33
|
+
SELECT
|
|
34
34
|
|
|
35
35
|
---
|
|
36
36
|
|
3
修正
test
CHANGED
|
@@ -40,9 +40,11 @@
|
|
|
40
40
|
|
|
41
41
|
step1 as (-- 対象項目を空白区切りで結合
|
|
42
42
|
|
|
43
|
-
select
|
|
43
|
+
select REPLACE(COALESCE(CAST(FA as varchar),'')+COALESCE(space(1) + CAST(FB as varchar), '')
|
|
44
44
|
|
|
45
|
-
+
|
|
45
|
+
+COALESCE(space(1) + CAST(FC as varchar), '')+COALESCE(space(1) + CAST(FD as varchar), '')
|
|
46
|
+
|
|
47
|
+
, ' ', space(1)) as note
|
|
46
48
|
|
|
47
49
|
from tbl
|
|
48
50
|
|
|
@@ -76,4 +78,4 @@
|
|
|
76
78
|
|
|
77
79
|
ネストせずに記述もできますが、さすがに可読性が半端なく低下するので、WITH式でネストしています。
|
|
78
80
|
|
|
79
|
-
※可読性を考慮しWITH式に修正
|
|
81
|
+
※可読性を考慮しWITH式に修正。REPLACE部分が冗長だったので修正。
|
2
修正
test
CHANGED
|
@@ -36,38 +36,44 @@
|
|
|
36
36
|
|
|
37
37
|
```SQL
|
|
38
38
|
|
|
39
|
+
with
|
|
40
|
+
|
|
41
|
+
step1 as (-- 対象項目を空白区切りで結合
|
|
42
|
+
|
|
43
|
+
select COALESCE(REPLACE(CAST(FA as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FB as varchar), ' ', ' '), '')
|
|
44
|
+
|
|
45
|
+
+ COALESCE(' ' + REPLACE(CAST(FC as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FD as varchar), ' ', ' '), '') as note
|
|
46
|
+
|
|
47
|
+
from tbl
|
|
48
|
+
|
|
49
|
+
),
|
|
50
|
+
|
|
51
|
+
step2 as (-- キーワードの位置算定
|
|
52
|
+
|
|
53
|
+
select note, CHARINDEX(case when CHARINDEX('様', note)>0 then '様' else '殿' end, note) as ptn_idx from step1
|
|
54
|
+
|
|
55
|
+
),
|
|
56
|
+
|
|
57
|
+
step3 as (-- キーワードの位置までの文字列を逆転
|
|
58
|
+
|
|
59
|
+
select note, ptn_idx, REVERSE(SUBSTRING(note, 1, ptn_idx)) as rvrs_str from step2
|
|
60
|
+
|
|
61
|
+
),
|
|
62
|
+
|
|
63
|
+
step4 as (-- 逆転した文字列の空白位置を算定
|
|
64
|
+
|
|
65
|
+
select note, rvrs_str, CHARINDEX(space(1), rvrs_str) as split_idx from step3
|
|
66
|
+
|
|
67
|
+
)
|
|
68
|
+
|
|
39
69
|
-- 逆転した文字列の空白位置までを取り出し再度逆転
|
|
40
70
|
|
|
41
71
|
select REVERSE(case when split_idx>0 then SUBSTRING(rvrs_str, 1, split_idx-1) else rvrs_str end) fchr, note
|
|
42
72
|
|
|
43
|
-
from ( -- 逆転した文字列の空白位置を算定
|
|
44
|
-
|
|
45
|
-
select note, rvrs_str, CHARINDEX(space(1), rvrs_str) as split_idx
|
|
46
|
-
|
|
47
|
-
from ( -- キーワードの位置までの文字列を逆転
|
|
48
|
-
|
|
49
|
-
select note, ptn_idx, REVERSE(SUBSTRING(note, 1, ptn_idx)) as rvrs_str
|
|
50
|
-
|
|
51
|
-
from ( -- キーワードの位置算定
|
|
52
|
-
|
|
53
|
-
select note, CHARINDEX(case when CHARINDEX('様', note)>0 then '様' else '殿' end, note) as ptn_idx
|
|
54
|
-
|
|
55
|
-
from ( -- 対象項目を空白区切りで結合
|
|
56
|
-
|
|
57
|
-
select COALESCE(REPLACE(CAST(FA as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FB as varchar), ' ', ' '), '')
|
|
58
|
-
|
|
59
|
-
+ COALESCE(' ' + REPLACE(CAST(FC as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FD as varchar), ' ', ' '), '') as note
|
|
60
|
-
|
|
61
|
-
|
|
73
|
+
from step4
|
|
62
|
-
|
|
63
|
-
) step1
|
|
64
|
-
|
|
65
|
-
) step2
|
|
66
|
-
|
|
67
|
-
) step3
|
|
68
|
-
|
|
69
|
-
) step4
|
|
70
74
|
|
|
71
75
|
```
|
|
72
76
|
|
|
73
|
-
ネストせずに記述もできますが、さすがに可読性が半端なく低下するので、ネストしています。
|
|
77
|
+
ネストせずに記述もできますが、さすがに可読性が半端なく低下するので、WITH式でネストしています。
|
|
78
|
+
|
|
79
|
+
※可読性を考慮しWITH式に修正
|
1
修正
test
CHANGED
|
@@ -54,9 +54,9 @@
|
|
|
54
54
|
|
|
55
55
|
from ( -- 対象項目を空白区切りで結合
|
|
56
56
|
|
|
57
|
-
select REPLACE(CAST(FA as varchar), '
|
|
57
|
+
select COALESCE(REPLACE(CAST(FA as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FB as varchar), ' ', ' '), '')
|
|
58
58
|
|
|
59
|
-
+ ' ' + REPLACE(CAST(FC as varchar), '
|
|
59
|
+
+ COALESCE(' ' + REPLACE(CAST(FC as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FD as varchar), ' ', ' '), '') as note
|
|
60
60
|
|
|
61
61
|
from tbl
|
|
62
62
|
|