How to extract string between variable characters?

Question

How to extract string between variable characters?

C-M-A

SSC-Addicted

Points: 407
More actions
October 1, 2020 at 4:57 pm

#3792315

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!

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mike01 SSChampion Points: 11395 More actions · Answer 2

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: 90303 More actions · Answer 3

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

C-M-A SSC-Addicted Points: 407 More actions · Answer 4

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: 101098 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".