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 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), (@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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy