多くの場合、T-SQLクエリは膨大な集合計算や高度な結合、インデックス戦略をコンパイルするためだけのものではありません。 時には、基本的な文字列解析を行うためのスキルやツールが必要になることもあります。
最もよく使われる文字列関数は、LEFT()とRIGHT()、そしてSUBSTRING()です。 LEFT(a, n)は “a “の最初の “n “文字を返します。これはBasicや他の多くのプログラミング言語で期待されるようなものです。 RIGHT(a, n)も同様に動作しますが、返される文字列は末尾から “n “文字となります。 SUBSTRING(a, offset, length)は、VisualBasicのMid()関数に対応しており、「offset」から始まる「a」から「length」文字を返します。
DECLARE @str varchar(100);SET @str='Another brown fox jumps over something';SELECT LEFT(@str, 7), SUBSTRING(@str, 15, 3), RIGHT(@str, 9), LEN(@str);
文字やワイルドカードの検索
T-SQLで文字列やワイルドカードを検索するには、基本的にCHARINDEX()とPATINDEX()という2つの異なる関数があります。 これらはほとんど同じように動作しますが、CHARINDEX(s, a)は「a」の中の文字列「s」の最初のオフセットを返すのに対し、PATINDEX(s, a)は「a」の中でワイルドカード「s」が真となる最初のオフセットを返すという違いがあります。 ワイルドカードは、LIKE句で適用するのと同じ構文を使用します。
DECLARE @str varchar(100);SET @str='Another brown fox jumps over something';SELECT CHARINDEX('fox', @str), PATINDEX('%jump%', @str);
文字列の値を分割する
これで基本的には文字列を例えば単語に分割することができます。
DECLARE @str varchar(100), @offset int;SET @str='Another brown fox jumps over something';WHILE (@str!='') BEGIN; SET @offset=CHARINDEX(' ', @str+' '); SELECT LEFT(@str, @offset-1); SET @str=SUBSTRING(@str, @offset+1, LEN(@str));END;
上記のクエリが行うことは次のとおりです。
- @strが空白になるまでループします。
- @offset の左側のすべてを選択します (ここでは @offset-1 を使用しているので、スペース自体は含まれません)
- @str の先頭を @offset+1 (スペースを含むため) の文字で切り捨てます。
- @str から最後の単語を切り捨てたら、変数は空白になり、ループは停止します。
…または再帰的な CTE を使用する
精巧さにさらにポイントを置きたい場合は、再帰的な共通テーブル式を使用します。
DECLARE @str varchar(100), @offset int;SET @str='Another brown fox jumps over something';WITH words (word, str_remain)AS ( --- Anchor part SELECT CAST(NULL AS varchar(100)) AS word, @str AS str_remain UNION ALL --- Recursion SELECT LEFT(str_remain, CHARINDEX(' ', str_remain+' ')-1) AS word, SUBSTRING(str_remain, CHARINDEX(' ', str_remain+' ')+1, LEN(str_remain)) AS str_remain FROM words WHERE str_remain!='')SELECT wordFROM wordsWHERE word IS NOT NULL;
再帰的な共通テーブル式については別の記事で紹介します。
Share:
Print