C# と VB.NET の質問掲示板

SQLServer で文字列が登録されています。
文字列は3文字ずつの数字の連続になっています。
そこから、不要な文字列を削除（003,004を除く）して
取得することはできますか？

DBデータ　　　　　　　　分割　　　　　　　　　　　　　削除　　　　　　　　取得
'001002003004005' 　→　001　002　003　004　005 　→　001　002　005 　→　'001002005'
'003004005006007' 　→　003　004　005　006　007 　→　005　006　007 　→　'005006007'

になります。

■No97160 (魔界の仮面弁士) に追記
>> 関数を作らない方法はないということでしょうか？
> 関数無しで実装するなら、共通テーブル式で再帰問い合わせをして、
> STRING_AGG 関数あるいは FOR XML PATH 式で連結するとか。

こんな感じかな…。
手元に環境が無いので未検証です。


-- サンプルデータ
CREATE TABLE Sample
 (id int PRIMARY KEY, text nvarchar(100) NULL);


INSERT INTO Sample VALUES
 (1, '001002003004005')
,(2, '003004005006007')
,(3, '003004003004003')
,(4, '1234567')
,(5, '')
,(6, NULL)
;


-- STRING_AGG 版 --
WITH CTE (id, text, pos, chunk) AS (
  SELECT
    Sample.id, Sample.text, pos = CAST(1 AS INT), SUBSTRING(Sample.text, 1, 3) AS chunk
  FROM
    Sample
  UNION ALL
  SELECT
    CTE.id, CTE.text, CTE.pos + 3, SUBSTRING(CTE.text, CTE.pos + 3, 3)
  FROM
    CTE
  WHERE
    ISNULL(SUBSTRING(CTE.text, CTE.pos + 3, 3), '') <> ''
)
SELECT
  CTE.id
, result = STRING_AGG(chunk, '') WITHIN GROUP (ORDER BY CTE.pos)
FROM
  CTE
WHERE
  CTE.chunk NOT IN ('003', '004')
GROUP BY
  CTE.id
ORDER BY
  CTE.id
;


-- FOR XML PATH 版 --
WITH CTE (id, text, pos, chunk) AS (
  SELECT
    Sample.id, Sample.text, pos = CAST(1 AS INT), SUBSTRING(Sample.text, 1, 3) AS chunk
  FROM
    Sample
  UNION ALL
  SELECT
    CTE.id, CTE.text, CTE.pos + 3, SUBSTRING(CTE.text, CTE.pos + 3, 3)
  FROM
    CTE
  WHERE
    ISNULL(SUBSTRING(CTE.text, CTE.pos + 3, 3), '') <> ''
), CTE2 AS (
  SELECT
    CTE.id
  , CTE.pos
  , CTE.chunk
  , CTE.text
  FROM
    CTE
  WHERE
    ISNULL(CTE.chunk, '') NOT IN ('003', '004')
)
SELECT DISTINCT
  CTE2.id
, CTE2.text
, result = (
    SELECT CTE3.chunk+''
	FROM CTE2 AS CTE3
	WHERE CTE3.id = CTE2.id
	ORDER BY CTE3.pos
	FOR XML PATH('')
  )
FROM
  CTE2
ORDER BY
  CTE2.id
;

■No97162 (魔界の仮面弁士) に追記
> こんな感じかな…。
> 手元に環境が無いので未検証です。

未検証のままにするのもアレなので、SQL Server 2019 Express を
インストールして確認してみましたが、いずれも、
それぞれ微妙に動作が異なっていました。orz


特定のパターンにおいては、これらいずれも要件を満たせていない可能性がありますので、
正常系だけでなく、異常系データについても検証しておいてくださいね。

（ひとまず、解決済みチェックは付けたままにしておきます）


No97148 の関数　… id = 4 のレコードが 7 文字ではなく 6 文字になってしまう

1: '001002003004005' → '001002005'
2: '003004005006007' → '005006007'
3: '003004003004003' → ''
4: '1234567' → '123456'
5: '' → ''
6: NULL → NULL



No97148 改　… 「WHILE @pos < @len」を「WHILE @pos <= @len」に変更したもの

1: '001002003004005' → '001002005'
2: '003004005006007' → '005006007'
3: '003004003004003' → ''
4: '1234567' → '1234567'
5: '' → ''
6: NULL → NULL



No97162 の STRING_AGG 版　… id = 3 と id = 6 が抽出されていない

1: '001002003004005' → '001002005'
2: '003004005006007' → '005006007'
4: '1234567' → '1234567'
5: '' → ''



No97162 の FOR XML PATH 版　… id = 3 が抽出されず、id = 6 が NULL ではなく '' になっている

1: '001002003004005' → '001002005'
2: '003004005006007' → '005006007'
4: '1234567' → '1234567'
5: '' → ''
6: NULL → ''