SQLで指定文字の前を抽出したい

Question

###前提・実現したいこと
SQLで一部文字列を抽出する方法はありますか？
例：メールアドレスからの「＠」の前を抽出
例：埼玉県、神奈川県、千葉県…等のリストから「県」の前を抽出

初めの○文字等、決まった文字数を一律で抽出する方法はわかるのですが、
上記のような指定文字の前の部分を抽出する方法はありますでしょうか。

恐れ入りますが、どうぞよろしくお願い致します。

###補足情報(言語/FW/ツール等のバージョンなど)
Microsoft SQL Server Management Studioを使用

Answer 1

都道府県の場合だと県を含むとは限らないので こんな感じで

SELECT LEFT(都道府県, CASE WHEN CHARINDEX('県', 都道府県) <> 0 THEN CHARINDEX('県', 都道府県) - 1 ELSE 0 END) AS 県名

CHARINDEX の代わりに PATINDEX を使うと後方一致や前方一致も出来ます。

Comments

[aimi]

このやり方ですとメールアドレスの方もうまくいきました！
ありがとうございます！！！

Answer 2

試してませんが、こんな感じでできないでしょうか。
必ず@が含まれている前提ですが。

SELECT SUBSTRING(mail,1,CHARINDEX(mail,'@')-1)

NULLと@を含まない場合の対策版

SELECT
  CASE mail
    WHEN NULL THEN
      NULL
    ELSE
      SUBSTRING(mail,1,CASE CHARINDEX('@',mail) WHEN 0 THEN LEN(mail) ELSE CHARINDEX('@',mail)-1 END)
  END;

Comments

[aimi]

ご回答ありがとうございます。
必ず＠は含まれているもので試してみましたが、エラーになってしまいました。
Invalid length parameter passed to the LEFT or SUBSTRING function.

[ttyp03]

すみません。こうかも。
CHARINDEXのパラメーターが逆でした。
SELECT SUBSTRING(mail,1,CHARINDEX('@',mail)-1)

[aimi]

ありがとうございます。
一瞬回り上手くいきそうだったのですが、３秒ほどすると先ほどと同様のエラーが出てしましいました。。

[ttyp03]

3秒ってのがよくわかりませんが、@を含んでないデータだったのでは。
なんか対策考えてみます。

[aimi]

なるほど、ちょっと調べられていないですが、一部＠を含んでいないデータが混ざっている可能性もあります。（NULL等）ありがとうございます。

[ttyp03]

NULLまたは＠を含んでいない場合の対策版を追記しておきました。
ちょっと長ったらしいですが・・・。
参考まで。

[aimi]

返信が遅れてしまい申し訳ありません。
CASEで条件分岐すればよいのですね！こちらうまく動きました。ありがとうございます!!

Answer 3

Oracleで@が必ずあるのが前提で、

SELECT SUBSTR(MAIL_ADDR, 1, INSTR(MAIL_ADDR, '@') -1) FROM
(SELECT '[e-mail]' AS MAIL_ADDR FROM DUAL) ;

SQLはデータベースによって方言がありますから、お使いのデータベースを明記した方が良いですよ。

Comments

[aimi]

ご回答ありがとうございます。
補足情報に書いてあったのですが、Microsoftでした。
Oracleを使用する機会があればこちらも試してみたいと思います。ありがとうございます！

Answer 4

mysqlとpostgresではpositionは使える様です

select substr('[e-mail]',1,position('@' in '[e-mail]')-1);


select substr('埼玉県ほげほげ市ほげほげ町',1,position('県' in '埼玉県ほげほげ市ほげほげ町')-1);

Comments

[aimi]

ご回答ありがとうございます。
このようなやり方もあるのですね。
ダメ元で回してみましたが、Microsoftのなので使えませんでした。

[A.Ichi]

SQLServerでしたか、お役に立てず失礼いたしました。
SELECT SUBSTRING('[e-mail]',1,CHARINDEX('@', '[e-mail]')-1);

[aimi]

いえいえとんでもないです。
Microsoftでも使えるものを教えていだきありがとうございます。
こちら使用させて頂きますm(_ _)m