如何在大描述值中找到字符串
16lz
2021-01-22
I have 2 column ID and description.I want to get the only mailid for each ID column.
我有两个列ID和描述。我想为每个ID列找到唯一的mailid。
ID Description
1 I have 2 mailID please note this mai1: anto1@gmail.com and mai1: anto2@gmail.com and mai1: anto3@gmail.com abbaaabbbbbbb.
2 I have 2 mailID please note this mai1: sample1@gmail.com and mai1: sample2@gmail.com and mai1: sample3@gmail.com abbaaabbbbbbb.
Expected output
预期的输出
ID Description
1 anto1@gmail.com
1 anto2@gmail.com
1 anto3@gmail.com
2 sample1@gmail.com
2 sample2@gmail.com
2 sample3@gmail.com
I have tried this below query.
我尝试过以下查询。
SELECT id,Description
FROM sample_for
WHERE CHARINDEX('@', Description) > 0
But please provide the alternate valid query.
但请提供另一个有效查询。
4 个解决方案
#1
2
Maybe something like this...
也许是这样的…
Test Data
Declare @table TABLE(ID int , Description Varchar(8000))
INsert into @table values
(1 , 'I have 2 mailID please note this mai1: anto1@gmail.com and mai1: anto2@gmail.com and mai1: anto3@gmail.com abbaaabbbbbbb'),
(2 , 'I have 2 mailID please note this mai1: sample1@gmail.com and mai1: sample2@gmail.com and mai1: sample3@gmail.com abbaaabbbbbbb')
Query
Select ID
,LEFT(RTRIM(LTRIM(Emails)) , CHARINDEX(' ' , RTRIM(LTRIM(Emails)))) Emails
from
(
SELECT t.ID
,Split.a.value('.', 'VARCHAR(100)') Emails
FROM
(SELECT Cast ('<X>' + Replace(Description, ':', '</X><X>') + '</X>' AS XML) AS Data
,ID
FROM @table
) AS t CROSS APPLY Data.nodes ('/X') AS Split(a)
)a
Where a.emails LIKE '%@%'
Result Set
╔════╦════════════════════╗
║ ID ║ Emails ║
╠════╬════════════════════╣
║ 1 ║ anto1@gmail.com ║
║ 1 ║ anto2@gmail.com ║
║ 1 ║ anto3@gmail.com ║
║ 2 ║ sample1@gmail.com ║
║ 2 ║ sample2@gmail.com ║
║ 2 ║ sample3@gmail.com ║
╚════╩════════════════════╝
更多相关文章
- PLSQL乱码解决方案
- 一种在t-SQL中选择两个日期之间的日期的方法
- 数据库不支持中文解决方案(mysql)
- 找到另一个列至少有两个不同值的id
- Mysql替代解决方案Cassandra
- [置顶] Android屏幕适配解决方案
- 物流货运移动APP解决方案
- 如何在函数中将两个参数从1个类传递给另一个？
- 转：Android Studio Error:Connection timed out: connect.解决方