如何在大描述值中找到字符串

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: [e-mail] and mai1: [e-mail] and mai1: [e-mail] abbaaabbbbbbb.
2   I have 2 mailID please note this mai1: [e-mail] and mai1: [e-mail] and mai1: [e-mail] abbaaabbbbbbb.

Expected output

预期的输出

ID  Description
1   [e-mail]
1   [e-mail]
1   [e-mail]
2   [e-mail]
2   [e-mail]
2   [e-mail]

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

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: [e-mail] and mai1: [e-mail] and mai1: [e-mail] abbaaabbbbbbb'),
(2 ,  'I have 2 mailID please note this mai1: [e-mail] and mai1: [e-mail] and mai1: [e-mail] 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 ║ [e-mail]    ║
║  1 ║ [e-mail]    ║
║  1 ║ [e-mail]    ║
║  2 ║ [e-mail]  ║
║  2 ║ [e-mail]  ║
║  2 ║ [e-mail]  ║
╚════╩════════════════════╝

#2

DECLARE @xml xml
--- Remove from here...    
;WITH cte AS (
SELECT *
FROM (VALUES
(1, 'I have 2 mailID please note this mai1: [e-mail] and mai1: [e-mail] and mai1: [e-mail] abbaaabbbbbbb.'),
(2, 'I have 2 mailID please note this mai1: [e-mail] and mai1: [e-mail] and mai1: [e-mail] abbaaabbbbbbb.')
) as t(ID, [Description])
)
-- To here
SELECT @xml = (
    SELECT CAST('<i id="' + CAST(id as nvarchar(10)) + '"><a>' +REPLACE([Description],' ','</a><a>') +'</a></i>' as xml)
    FROM cte -- here change cte to your table name
    FOR XML PATH ('')
)

SELECT  t.v.value('../@id', 'int') as id,
        t.v.value('.', 'nvarchar(100)') as email
FROM @xml.nodes('/i/a') as t(v)
WHERE t.v.value('.', 'nvarchar(100)') like '%@%'

Output:

输出:

id  email
1   [e-mail]
1   [e-mail]
1   [e-mail]
2   [e-mail]
2   [e-mail]
2   [e-mail]

#3

SELECT SUBSTRING(Description, CHARINDEX('mai1:', Description) + 5, CHARINDEX('mai1:', Description))
FROM Table
WHERE Description LIKE '%mai1:%'
  AND CHARINDEX('mai1:', Description) > 0

#4

Not as robust a solution due to my limited knowledge of SQL server (would probably do this with REGEXP_SUBSTR in oracle). It's limited to 3 or less emails and would have to be expanded manually if more are needed. One of the other solutions posted are probably better but just wanted to put this on here as well:

由于我对SQL server的了解有限，这个解决方案没有那么健壮(在oracle中使用REGEXP_SUBSTR可能会这样做)。它仅限于3封或更少的电子邮件，如果需要更多的邮件，就必须手动扩展。其他的解决方案中有一个可能更好，但我也想把它写在这里:

with temp(ID,  Description)
as(
select 1, 'I have 2 mailID please note this mai1: [e-mail] and mai1: [e-mail] and mai1: [e-mail] abbaaabbbbbbb.' union all
select 2, 'I have 2 mailID please note this mai1: [e-mail] and mai1: [e-mail] and mai1: [e-mail] abbaaabbbbbbb.'
)

SELECT
 ID,
 EMAIL
from (
  select
  ID,
  case when start1 > 0 then substring(Description, start1+2, end1-start1+2) end email1,
  case when start2 > 0 then substring(Description, start2+2, end2-start2+2) end email2,
  case when start3 > 0 then substring(Description, start3+2, end3-start3+2) end email3
  from (
    select ID, Description,
      start1 = ISNULL(CHARINDEX(':', Description, 0),0),
      start2 = ISNULL(CHARINDEX(':', Description, CHARINDEX(':', Description, 0)+1),0),
      start3 = ISNULL(CHARINDEX(':', Description, CHARINDEX(':', Description, CHARINDEX(':', Description, 0)+1)+1),0),
      end1 = ISNULL(CHARINDEX('.com', Description, 0),0),
      end2 = ISNULL(CHARINDEX('.com', Description, CHARINDEX('.com', Description, 0)+4),0),
      end3 = ISNULL(CHARINDEX('.com', Description, CHARINDEX('.com', Description, CHARINDEX('.com', Description, 0)+4)+4),0)
    from temp  ) as pos
    ) temp1
CROSS APPLY
(
  VALUES
  (email1),
  (email2),
  (email3)
) temp2 (email)
where EMAIL is NOT NULL