How to extract string between variable characters?

  • C-M-A

    Old Hand

    Points: 388

    Hi All,

    I've got a nasty column that I need to get the name part of the first email address and the name part of the second email address. For example, I would want:

    Person.Lastname and Second.Person

    From the below examples:

    b: 9/22/2020 2:50:23 PM : Person.Lastname@Company.com as Second.Person@Company.com : Approved

    a: 9/22/2020 1:59 PM : Person.Lastname@Company.com as Second.Person@Company.com : Duplicate

    My problem is that the ':' occurs sometimes 3 times or 4 times so it isn't consistent. and I'm not entirely sure how to get the name in the second email address either. This seems like a simple matter of using substring and charindex, but the inconsistent number of ':' is really throwing me off.

    Any suggestions?

    Thanks in advance!

  • Phil Parkin

    SSC Guru

    Points: 244792

    Not sure how you want the output formatted, but does this help?

    DROP TABLE IF EXISTS #Stuff;

    CREATE TABLE #Stuff
    (
    StuffId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
    ,EmailText VARCHAR(1000) NOT NULL
    );

    INSERT #Stuff
    (
    EmailText
    )
    VALUES
    ('b: 9/22/2020 2:50:23 PM : Person.Lastname@Company.com as Second.Person@Company.com : Approved')
    ,('a: 9/22/2020 1:59 PM : Person.Lastname@Company.com as Second.Person@Company.com : Duplicate');

    SELECT s.StuffId
    ,split.value
    FROM #Stuff s
    CROSS APPLY
    (
    SELECT ss.value
    FROM STRING_SPLIT(s.EmailText, ' ') ss
    WHERE CHARINDEX('@', ss.value) > 0
    ) split;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Mike01

    SSChampion

    Points: 11298

    Phil,

    that's brilliant even though it feels dirty 😉

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Jeffrey Williams

    SSC Guru

    Points: 88705

    If you need everything in a single row - then yes, you can use charindex and substring, along with reverse...and rtrim/ltrim to eliminate unwanted spaces.

    Declare @testTable Table (TestString varchar(100))

    Insert Into @testTable (TestString)
    Values ('b: 9/22/2020 2:50:23 PM : Person.Lastname@Company.com as Second.Person@Company.com : Approved')
    , ('a: 9/22/2020 1:59 PM : Person.Lastname@Company.com as Second.Person@Company.com : Duplicate');

    Select *
    , FirstEmailName = rtrim(ltrim(substring(e1.EmailAddress, 1, charindex('@', e1.EmailAddress, 1) - 1)))
    , SecondEmailName = rtrim(ltrim(substring(e2.EmailAddress, 1, charindex('@', e2.EmailAddress, 1) - 1)))
    From @testTable tt
    Cross Apply (Values (reverse(tt.TestString))) As r(TextString)
    Cross Apply (Values (charindex(':', r.TextString, 1) + 1)) As p1(pos)
    Cross Apply (Values (charindex(':', r.TextString, p1.pos) + 1)) As p2(pos)
    Cross Apply (Values (reverse(ltrim(rtrim(substring(r.TextString, p1.pos, p2.pos - p1.pos - 1)))))) As a(EmailAddresses)
    Cross Apply (Values (charindex(' as ', a.EmailAddresses + ' as ', 1))) As a1(pos)
    Cross Apply (Values (charindex(' as ', a.EmailAddresses + ' as ', a1.pos + 1))) As a2(pos)
    Cross Apply (Values (substring(a.EmailAddresses, 1, a2.pos - a1.pos - 1))) As e1(EmailAddress)
    Cross Apply (Values (substring(a.EmailAddresses, a1.pos + 4, a2.pos - a1.pos - 1))) As e2(EmailAddress);

    You could also use Phil's solution and crosstab/pivot the results back...but if you need the other elements then you can extend this to include the other portions of the string.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • C-M-A

    Old Hand

    Points: 388

    Thank you Jeffrey Williams,

    The last two columns in yours is exactly what I was after.

    Phils would also work with a little bit of tweaking.

    I appreciate you two

  • ScottPletcher

    SSC Guru

    Points: 98626

    If you need both extracted values in the same row:

    SELECT s.StuffId, ds2.email_name_1, ds2.email_name_2
    FROM #Stuff s
    CROSS APPLY (
    SELECT
    MAX(CASE WHEN row_num = 1 THEN email_name END) AS email_name_1,
    MAX(CASE WHEN row_num = 2 THEN email_name END) AS email_name_2 --,
    /*
    MAX(CASE WHEN row_num = 3 THEN email_name END) AS email_name_3, ...
    */
    FROM (
    SELECT LEFT(Item, CHARINDEX('@', Item) - 1) AS email_name,
    ROW_NUMBER() OVER(ORDER BY ds.ItemNumber) AS row_num
    FROM dbo.DelimitedSplit8K(EmailText, ' ') ds
    WHERE ds.Item LIKE '%@%.%'
    ) AS query1
    ) AS ds2

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply