Checking if a string value includes a value from a field

Question

Checking if a string value includes a value from a field

DSC123

SSChasing Mays

Points: 628
More actions
July 27, 2016 at 1:45 am

#328514

Morning all,
Please could someone advise me on the correct syntax for highlighting if an email address includes a year of birth?
So far I have created the following, the last line extracts the year of birth from the DOB field as YEAR_OF_BIRTH which I would like to see if it is included in the email address.
SELECT
CONVERT(DATE, (DOB)) AS DOB,
EMAIL,
LEFT(RIGHT(CONVERT(DATE, (DOB)),8),2) AS YEAR_OF_BIRTH
FROM
TABLE
What I would like is a Yes/No entry in a field if the year of birth is included in the email address field - I'm not sure how I compare the 2 fields.
Thanks in advance.
Daz

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

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

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

DSC123 (7/27/2016)
Morning all,
Please could someone advise me on the correct syntax for highlighting if an email address includes a year of birth?
So far I have created the following, the last line extracts the year of birth from the DOB field as YEAR_OF_BIRTH which I would like to see if it is included in the email address.
SELECT
CONVERT(DATE, (DOB)) AS DOB,
EMAIL,
LEFT(RIGHT(CONVERT(DATE, (DOB)),8),2) AS YEAR_OF_BIRTH
FROM
TABLE
What I would like is a Yes/No entry in a field if the year of birth is included in the email address field - I'm not sure how I compare the 2 fields.

Add something like this to your SELECT list?

CASE WHEN EMAIL LIKE '%' + LEFT(RIGHT(CONVERT(DATE,(DOB)),8),2) + '%@%' THEN 'Yes' ELSE 'No' END AS DOBYearInEmail

...

Thomas Rushton
blog: https://thelonedba.wordpress.com

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Quick suggestion, you can you PATINDEX to validate Email as well as to get the Birth Year from the email address.

select dob, adj.YEAR_OF_BIRTH, email, IsValidEmail

from

(

Select cast('20160101' as datetime) as dob, 'abc16@123.com' as email union all

Select cast('20160103' as datetime) as dob, 'abc1@abc.com' as email union all

Select cast('20060204' as datetime) as dob, 'abc06@abc123.com' as email union all

Select cast('20260101' as datetime) as dob, 'abc06xy.com' as email

) A

CROSS APPLY

(

Select Right(DATEPART(yy, dob),2) as YEAR_OF_BIRTH

, PATINDEX('%'+ Right(DATEPART(yy, dob),2) +'%' , LEFT(email, PATINDEX('%[A-Z0-9-]@[A-Z0-9-]%', email))) as IsValidEmail

) adj

--- uncomment this line to get the valid email list

-- Where Adj.IsValidEmail > 0

DSC123 SSChasing Mays Points: 628 More actions · Answer 3

Thank you both! The solution works perfectly. I was trying to do it in a sub select, didn't think to put the same year of birth calculation in the existing select statement.

Also thanks for the email validation - this will come in very handy, although I think it will give me quite a job of getting the data cleansed! :unsure:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

DSC123 (7/27/2016)
Morning all,
Please could someone advise me on the correct syntax for highlighting if an email address includes a year of birth?
So far I have created the following, the last line extracts the year of birth from the DOB field as YEAR_OF_BIRTH which I would like to see if it is included in the email address.
SELECT
CONVERT(DATE, (DOB)) AS DOB,
EMAIL,
LEFT(RIGHT(CONVERT(DATE, (DOB)),8),2) AS YEAR_OF_BIRTH
FROM
TABLE
What I would like is a Yes/No entry in a field if the year of birth is included in the email address field - I'm not sure how I compare the 2 fields.
Thanks in advance.
Daz

What datatype is DOB? It should be DATE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DSC123 SSChasing Mays Points: 628 More actions · Answer 5

Hi Chris - it's a date time field. It comes from a poorly built off the shelf product. I've converted to date as I am pulling the data into Excel and only want to see the date.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

twin.devil (7/27/2016)
Quick suggestion, you can you PATINDEX to validate Email as well as to get the Birth Year from the email address.
select dob, adj.YEAR_OF_BIRTH, email, IsValidEmail
from
(
Select cast('20160101' as datetime) as dob, 'abc16@123.com' as email union all
Select cast('20160103' as datetime) as dob, 'abc1@abc.com' as email union all
Select cast('20060204' as datetime) as dob, 'abc06@abc123.com' as email union all
Select cast('20260101' as datetime) as dob, 'abc06xy.com' as email union all
Select cast('20260101' as datetime) as dob, 'abc+mykey@domain.com' as email
) A
CROSS APPLY
(
Select Right(DATEPART(yy, dob),2) as YEAR_OF_BIRTH
, PATINDEX('%'+ Right(DATEPART(yy, dob),2) +'%' , LEFT(email, PATINDEX('%[A-Z0-9-]@[A-Z0-9-]%', email))) as IsValidEmail
) adj

Your validation flags legit email addresses as invalid. 'abc+mykey@domain.com' is valid.

Email validation is damn hard. It's worth reading over the RFCs to see what constitutes a valid email address

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass