t-sql 2012 use like statement

Question

t-sql 2012 use like statement

wendy elizabeth

SSCoach

Points: 16915
More actions
July 7, 2016 at 3:03 pm

#311569

In t-sql 2012, I want to compare the values of 2 columns the values of [Identity].MiddleName to ALF.SPA1_MiddleName. The [Identity].MiddleName will always have the correct name but the ALF.SPA1_MiddleName
field will usually only have part of the name. See t-sql below:
select *
from test1.dbo.DianeALFUser ALF
JOIN test2.DBO.[Identity] [Identity] WITH (NOLOCK)
on upper(ALF.SPA1_LastName) = upper([Identity].lastName)
and upper(ALF.SPA1_FirstName) = upper([Identity].firstName)
and (([Identity].MiddleName is NULL and ALF.SPA1_MiddleName is null)
OR ([Identity].MiddleName = ALF.SPA1_MiddleName ))
Thus can you show me how to use a like or wild card compare on the line listed below:
OR ([Identity].MiddleName = ALF.SPA1_MiddleName ))

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

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

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

wendy elizabeth (7/7/2016)
In t-sql 2012, I want to compare the values of 2 columns the values of [Identity].MiddleName to ALF.SPA1_MiddleName. The [Identity].MiddleName will always have the correct name but the ALF.SPA1_MiddleName
field will usually only have part of the name. See t-sql below:
select *
from test1.dbo.DianeALFUser ALF
JOIN test2.DBO.[Identity] [Identity] WITH (NOLOCK)
on upper(ALF.SPA1_LastName) = upper([Identity].lastName)
and upper(ALF.SPA1_FirstName) = upper([Identity].firstName)
and (([Identity].MiddleName is NULL and ALF.SPA1_MiddleName is null)
OR ([Identity].MiddleName = ALF.SPA1_MiddleName ))
Thus can you show me how to use a like or wild card compare on the line listed below:
OR ([Identity].MiddleName = ALF.SPA1_MiddleName ))

You've been here long enough: DDL, sample data and desired results, please.

Also, if your database is not case-sensitive, get rid of those UPPER() functions, they're wasting energy.

And finally, you know that NOLOCK means that you can read the same data twice, or data which is never committed to the database, I hope?

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

wendy elizabeth (7/7/2016)
In t-sql 2012, I want to compare the values of 2 columns the values of [Identity].MiddleName to ALF.SPA1_MiddleName. The [Identity].MiddleName will always have the correct name but the ALF.SPA1_MiddleName
field will usually only have part of the name. See t-sql below:
select *
from test1.dbo.DianeALFUser ALF
JOIN test2.DBO.[Identity] [Identity] WITH (NOLOCK)
on upper(ALF.SPA1_LastName) = upper([Identity].lastName)
and upper(ALF.SPA1_FirstName) = upper([Identity].firstName)
and (([Identity].MiddleName is NULL and ALF.SPA1_MiddleName is null)
OR ([Identity].MiddleName = ALF.SPA1_MiddleName ))
Thus can you show me how to use a like or wild card compare on the line listed below:
OR ([Identity].MiddleName = ALF.SPA1_MiddleName ))

MS documentation is mostly your friend here. Be advise that the use of any leading wildcard character will make it impossible to do an Index Seek.

https://msdn.microsoft.com/en-us/library/ms179859.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)