November 1, 2018 at 1:49 pm
I'm having issues getting some data out of a ntext field in SQL. Below is an example of some the data but I only need see the email address to the left of the ":Sent"
The data will always have the same spaces between each email and :Sent.
There are only 3 emails in my samples but there could be more data than this.
Field data examples:
Sample 1 - john.smith@gmail.com: completed, , 10/16/2018 5:44 PM frankThomas@gmail.com: sent, , marc.able@gmail.com: created, , Updated On 10/16/2018 at 6:03 PM
Sample 2 - john.smith@gmail.com: sent , 10/16/2018 5:44 PM frankThomas@gmail.com: created, , marc.able@gmail.com: created, , Updated On 10/16/2018 at 6:03 PM
Sample 3 - john.smith@gmail.com: completed, , 10/16/2018 5:44 PM frankThomas@gmail.com: completed, , marc.able@gmail.com: sent, , Updated On 10/16/2018 at 6:03 PM
Data needed
Sample 1 - frankThomas@gmail.com
Sample 2 - john.smith@gmail.com
Sample 3 - marc.able@gmail.com
Thanks in advance
November 1, 2018 at 2:10 pm
I have to say, CTE's make this sort of thing a lot easier. I am sure there is a better way, but how does this fit for now:
declare @sample1-2 nvarchar(3000) = 'john.smith@gmail.com: completed, , 10/16/2018 5:44 PM frankThomas@gmail.com: sent, , marc.able@gmail.com: created, , Updated On 10/16/2018 at 6:03 PM';
declare @Sample2 nvarchar(3000) = 'john.smith@gmail.com: sent , 10/16/2018 5:44 PM frankThomas@gmail.com: created, , marc.able@gmail.com: created, , Updated On 10/16/2018 at 6:03 PM';
declare @Sample3 nvarchar(3000) = 'john.smith@gmail.com: completed, , 10/16/2018 5:44 PM frankThomas@gmail.com: completed, , marc.able@gmail.com: sent, , Updated On 10/16/2018 at 6:03 PM';
with cut1 (cut)
as
(select substring (reverse(sample), charindex('tnes :', reverse(sample)) + 6 , 100)
from (values (@sample1-2), (@sample2), (@sample3)) as dat (sample))
select reverse(case when charindex (' ', cut) = 0 then cut
else substring (cut, 0, charindex (' ', cut)) end)
from cut1
November 1, 2018 at 2:17 pm
Thanks for the reply I will try to work with code but I forgot to mention the field name where the emails come from. The emails will always be different and not always the same.
I'm assuming I can replace the text in the declare string to the field name?
declare @sample1-2 nvarchar(3000) = @fieldname
November 1, 2018 at 2:23 pm
I just used the declare block to set up the test values. You can probably substitute your table where I have values (@sample1),(@sample2),(@sample3))as dat(sample)
The VALUES clause creates a temporary table, so I can get all the sample values in one shot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply