从数据库中获取子字符串 [英] Fetching the sub-string from the database

问题描述

我有一个表格，其中包含以下字符串的列。

I have a table with a column which contains strings like below.

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

[e-mail]

我需要从@ to。（dot）
i写了一些sql，但它有固定的长度，我需要动态查询来获取子字符串。

I need to get the substring from the @ to .(dot) i have writing some sql but it have fixed length i need the dynamic query to get the sub-string.

select * from registration where电子邮件喜欢'％@％';

select *from registration where email like '%@%';

子字符串查询
从注册中选择电子邮件，substr（电子邮件，

Sub string Query select email, substr(email,4,10) from registration;

请为我写一个查询。

推荐答案

do it：

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING_INDEX(email, '@', -1)), LOCATE('.', REVERSE(email))+1));

这将包括@和最后一个之间的所有点（例如[e-mail] .name.com将导致a.long.domain.name），而不是嵌套的SUBSTRING_INDEX，它只返回@和第一个点（例如a）之间的内容。

This will include all dots between @ and the final one (e.g. "[e-mail]" will result in "a.long.domain.name") as opposed to nested SUBSTRING_INDEX which will only return whats between @ and the first dot (e.g "a").

这篇关于从数据库中获取子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！