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

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

SQLServerからのデータ取得方法について

[トピック内 7 記事 (1 - 7 表示)]  << 0 >>

■97147 / inTopicNo.1)  SQLServerからのデータ取得方法について
  
□投稿者/ 紅雀 (5回)-(2021/04/07(Wed) 10:36:00)

分類:[データベース全般] 

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'

になります。

引用返信 編集キー/
■97148 / inTopicNo.2)  Re[1]: SQLServerからのデータ取得方法について
□投稿者/ 魔界の仮面弁士 (3044回)-(2021/04/07(Wed) 11:03:28)
2021/04/07(Wed) 12:27:45 編集(投稿者)

※ NULL を渡された時は、空文字ではなく NULL を返すように変更しました

No97147 (紅雀 さん) に返信
> 文字列は3文字ずつの数字の連続になっています。
> そこから、不要な文字列を削除(003,004を除く)して
> 取得することはできますか?

-- SUBSTRING で 3 文字ずつ切り出して、不要な文字列以外を繋ぎなおす
CREATE FUNCTION dbo.Wankuma97148(@text nvarchar(100))
 RETURNS nvarchar(100)
AS
BEGIN
 DECLARE @len int = LEN(@text)
 DECLARE @pos int = 1
 DECLARE @chunk nvarchar(3) = NULL
 DECLARE @result nvarchar(100) = CASE WHEN @text IS NOT NULL THEN '' END
 WHILE @pos < @len
 BEGIN
  SET @chunk = SUBSTRING(@text, @pos, 3)
  IF @chunk NOT IN (N'003', '004') SET @result = CONCAT(@result, @chunk)
  SET @pos = @pos + 3
 END
 RETURN @result
END
引用返信 編集キー/
■97159 / inTopicNo.3)  Re[2]: SQLServerからのデータ取得方法について
□投稿者/ 紅雀 (6回)-(2021/04/08(Thu) 09:10:02)
No97148 (魔界の仮面弁士 さん) に返信

関数を作って処理をするということですよね。

SELECT dbo.Wankuma97148(DATA);

関数を作らない方法はないということでしょうか?
引用返信 編集キー/
■97160 / inTopicNo.4)  Re[3]: SQLServerからのデータ取得方法について
□投稿者/ 魔界の仮面弁士 (3046回)-(2021/04/08(Thu) 09:50:43)
No97159 (紅雀 さん) に返信
> 関数を作らない方法はないということでしょうか?
関数無しで実装するなら、共通テーブル式で再帰問い合わせをして、
STRING_AGG 関数あるいは FOR XML PATH 式で連結するとか。

※ STRING_AGG の利用には SQL Server 2017 以降が必要です。
引用返信 編集キー/
■97162 / inTopicNo.5)  Re[4]: SQLServerからのデータ取得方法について
□投稿者/ 魔界の仮面弁士 (3048回)-(2021/04/08(Thu) 10:19:10)
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
;

引用返信 編集キー/
■97163 / inTopicNo.6)  Re[5]: SQLServerからのデータ取得方法について
□投稿者/ 紅雀 (7回)-(2021/04/08(Thu) 11:07:21)
No97162 (魔界の仮面弁士 さん) に返信

動作確認はできました。
再帰についてもう少し勉強して対応します。

ありがとうございました。
解決済み
引用返信 編集キー/
■97167 / inTopicNo.7)  Re[5]: SQLServerからのデータ取得方法について
□投稿者/ 魔界の仮面弁士 (3051回)-(2021/04/08(Thu) 13:31:56)
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 → ''

解決済み
引用返信 編集キー/

このトピックをツリーで一括表示


トピック内ページ移動 / << 0 >>

このトピックに書きこむ