SQL-извлечение текста между символами

Question

Вот как выглядят мои данные. (Я пытаюсь указать точный адрес электронной почты, чтобы можно было отправлять электронные письма для сотрудников TO и CC.)

    EmailTO:[url=mailto:[e-mail]] [e-mail][/url]             
    EmailCC:[url=mailto:[e-mail]] [e-mail][/url]           

    Hello, This is the rest of the email message....

Когда я запускаю этот первый SQL, я получаю желаемые результаты.

    Select
    Body,
    SUBSTRING(Body, CHARINDEX('EmailTO', Body) + 20,CHARINDEX(']',Body)-CHARINDEX('EmailTO',Body)-20) ToEmail

Это возвращает

    ToEmaiL = [e-mail]

Но когда я пытаюсь сделать второй SUBSTRING, как это

    Select
    Body,
    SUBSTRING(Body, CHARINDEX('EmailTO', Body) + 20,CHARINDEX(']',Body)-CHARINDEX('EmailTO',Body)-20) ToEmail,
    SUBSTRING(Body, CHARINDEX('EmailCC', Body) + 20,CHARINDEX(']',Body)-CHARINDEX('EmailCC',Body)-20) CCEmail --(Simply replacing the EmailTo from the previous line to EmailCC)
    From hdIssues   

Я получаю эту ошибку

    "Msg 537, Level 16, State 5, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function."

Любая помощь приветствуется.

PS В моем наборе данных адреса электронной почты могут иметь несколько получателей, разделенных точкой с запятой, например:

[url=mailto:[e-mail]] [e-mail][/url]; [url=mailto:[e-mail]] [e-mail][/url]; [url=mailto:[e-mail]] [e-mail][/url]

Answer 1

Если открыт для TVF

пример

Select A.ID
      ,B.*
 From  YourTable A
 Cross Apply [dbo].[tvf-Str-Extract](A.Body,'[url=mailto:',']') B

Возвращает

ID  RetSeq  RetPos  RetVal
1   1       23      [e-mail]
1   2       89      [e-mail]
1   3       155     [e-mail]
1   4       229     [e-mail]

TVF, если интересно

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (
        Select *,RetVal = Substring(@String, N, L) 
         From  cte4
       ) A
 Where charindex(@Delimiter2,RetVal)>1

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/

РЕДАКТИРОВАТЬ - Для тела

Два разделителя '[/url]' а также '|||', Затем принудительно завершаем разделитель, добавляя уникальную строку. В этом случае я выбрал |||

Если вы не хотите многократные записи. Удалить CROSS APPLY B

пример

Select A.ID
      ,B.*
      ,Body = ltrim(rtrim(C.RetVal))
 From  @YourTable A
 Cross Apply [dbo].[tvf-Str-Extract](A.Body,'[url=mailto:',']') B
 Cross Apply [dbo].[tvf-Str-Extract](A.Body+'|||','[/url]','|||') C  --- Notice A.Body+'|||'.... this is to force an ending delimiter

Возвращает

Answer 2

Я бы использовал regexp_substr

with t1(col) as(
   select 'EmailTO:[url=mailto:[e-mail]] [e-mail][/url]' from dual
)

select regexp_substr(col, '[[:alnum:]._%-]+@[[:alnum:]._%-]+\.com') as res
  from t1;

Это вытянет оба адреса электронной почты, которые я оставил, потому что вы сказали в своем PS, что могут существовать несколько адресов электронной почты. Вы можете изменить регулярное выражение, чтобы вытащить только одну копию каждого письма.

Answer 3

Чтобы решить проблему с вашим запросом, вам нужно начать поиск EmailCC после первого символа ']'. В противном случае вы берете первое вхождение символа ']', ДО которого 'EmailCC' и, следовательно, ошибка. Вы можете сделать это, добавив start_location для CHARINDEX().

Поэтому измените ваш запрос на следующий:

    Select
    Body,
    SUBSTRING(Body, CHARINDEX('EmailTO', Body) + 20,CHARINDEX(']',Body)-CHARINDEX('EmailTO',Body)-20) ToEmail,
    SUBSTRING(Body, CHARINDEX('EmailCC', Body) + 20,CHARINDEX(']',Body, CHARINDEX('EmailCC', Body))-CHARINDEX('EmailCC',Body)-20) CCEmail
    From hdIssues

Просмотрите документы здесь: https://docs.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql