回答編集履歴
4
誤字
answer
CHANGED
|
@@ -14,7 +14,7 @@
|
|
|
14
14
|
(5,'名刺が必要','来週対応','休み','お酒NG')
|
|
15
15
|
;
|
|
16
16
|
```
|
|
17
|
-
|
|
17
|
+
SELECT
|
|
18
18
|
---
|
|
19
19
|
```SQL
|
|
20
20
|
with
|
3
修正
answer
CHANGED
|
@@ -19,8 +19,9 @@
|
|
|
19
19
|
```SQL
|
|
20
20
|
with
|
|
21
21
|
step1 as (-- 対象項目を空白区切りで結合
|
|
22
|
-
select
|
|
22
|
+
select REPLACE(COALESCE(CAST(FA as varchar),'')+COALESCE(space(1) + CAST(FB as varchar), '')
|
|
23
|
-
|
|
23
|
+
+COALESCE(space(1) + CAST(FC as varchar), '')+COALESCE(space(1) + CAST(FD as varchar), '')
|
|
24
|
+
, ' ', space(1)) as note
|
|
24
25
|
from tbl
|
|
25
26
|
),
|
|
26
27
|
step2 as (-- キーワードの位置算定
|
|
@@ -37,4 +38,4 @@
|
|
|
37
38
|
from step4
|
|
38
39
|
```
|
|
39
40
|
ネストせずに記述もできますが、さすがに可読性が半端なく低下するので、WITH式でネストしています。
|
|
40
|
-
※可読性を考慮しWITH式に修正
|
|
41
|
+
※可読性を考慮しWITH式に修正。REPLACE部分が冗長だったので修正。
|
2
修正
answer
CHANGED
|
@@ -17,21 +17,24 @@
|
|
|
17
17
|
SELECE
|
|
18
18
|
---
|
|
19
19
|
```SQL
|
|
20
|
+
with
|
|
21
|
+
step1 as (-- 対象項目を空白区切りで結合
|
|
22
|
+
select COALESCE(REPLACE(CAST(FA as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FB as varchar), ' ', ' '), '')
|
|
23
|
+
+ COALESCE(' ' + REPLACE(CAST(FC as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FD as varchar), ' ', ' '), '') as note
|
|
24
|
+
from tbl
|
|
25
|
+
),
|
|
26
|
+
step2 as (-- キーワードの位置算定
|
|
27
|
+
select note, CHARINDEX(case when CHARINDEX('様', note)>0 then '様' else '殿' end, note) as ptn_idx from step1
|
|
28
|
+
),
|
|
29
|
+
step3 as (-- キーワードの位置までの文字列を逆転
|
|
30
|
+
select note, ptn_idx, REVERSE(SUBSTRING(note, 1, ptn_idx)) as rvrs_str from step2
|
|
31
|
+
),
|
|
32
|
+
step4 as (-- 逆転した文字列の空白位置を算定
|
|
33
|
+
select note, rvrs_str, CHARINDEX(space(1), rvrs_str) as split_idx from step3
|
|
34
|
+
)
|
|
20
35
|
-- 逆転した文字列の空白位置までを取り出し再度逆転
|
|
21
36
|
select REVERSE(case when split_idx>0 then SUBSTRING(rvrs_str, 1, split_idx-1) else rvrs_str end) fchr, note
|
|
22
|
-
from ( -- 逆転した文字列の空白位置を算定
|
|
23
|
-
select note, rvrs_str, CHARINDEX(space(1), rvrs_str) as split_idx
|
|
24
|
-
from ( -- キーワードの位置までの文字列を逆転
|
|
25
|
-
select note, ptn_idx, REVERSE(SUBSTRING(note, 1, ptn_idx)) as rvrs_str
|
|
26
|
-
from ( -- キーワードの位置算定
|
|
27
|
-
select note, CHARINDEX(case when CHARINDEX('様', note)>0 then '様' else '殿' end, note) as ptn_idx
|
|
28
|
-
from ( -- 対象項目を空白区切りで結合
|
|
29
|
-
select COALESCE(REPLACE(CAST(FA as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FB as varchar), ' ', ' '), '')
|
|
30
|
-
+ COALESCE(' ' + REPLACE(CAST(FC as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FD as varchar), ' ', ' '), '') as note
|
|
31
|
-
|
|
37
|
+
from step4
|
|
32
|
-
) step1
|
|
33
|
-
) step2
|
|
34
|
-
) step3
|
|
35
|
-
) step4
|
|
36
38
|
```
|
|
37
|
-
ネストせずに記述もできますが、さすがに可読性が半端なく低下するので、ネストしています。
|
|
39
|
+
ネストせずに記述もできますが、さすがに可読性が半端なく低下するので、WITH式でネストしています。
|
|
40
|
+
※可読性を考慮しWITH式に修正
|
1
修正
answer
CHANGED
|
@@ -26,8 +26,8 @@
|
|
|
26
26
|
from ( -- キーワードの位置算定
|
|
27
27
|
select note, CHARINDEX(case when CHARINDEX('様', note)>0 then '様' else '殿' end, note) as ptn_idx
|
|
28
28
|
from ( -- 対象項目を空白区切りで結合
|
|
29
|
-
select REPLACE(CAST(FA as varchar), '
|
|
29
|
+
select COALESCE(REPLACE(CAST(FA as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FB as varchar), ' ', ' '), '')
|
|
30
|
-
+ ' ' + REPLACE(CAST(FC as varchar), '
|
|
30
|
+
+ COALESCE(' ' + REPLACE(CAST(FC as varchar), ' ', ' '), '') + COALESCE(' ' + REPLACE(CAST(FD as varchar), ' ', ' '), '') as note
|
|
31
31
|
from tbl
|
|
32
32
|
) step1
|
|
33
33
|
) step2
|