T-SQL: Is there a better way to write 'where column_name like '%CS?

Hi Experts,
I would like to do an update. What would be the best way to do it if mail_cd is always 5 characters and I'd like to capture the last two characters only? This is how I'm doing it now but I'm curious if there is a better way to do it:

update Employees set Department ='Customer Service' where mail_cd like '%CS'
romsomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That way is fine, and wold be better performance only if Department and mail_cd were indexed.

Otherwise, RIGHT(mail_cd, 2) works, SUBSTRING(mail_cd, 4, 2) works too.

>mail_cd is always 5 characters
Might not be a bad ideal to run the below just to make sure:

SELECT mail_cd FROM Employees WHERE LEN(mail_cd) <> 5
0
 
ste5anSenior DeveloperCommented:
Nope, as long as you don't want to use indices.

In this case create a persisted, computed colum of REVERSE(mail_cd) and create an index on this column. Then search for
 WHERE reversed_mail_cd LIKE REVERSE('%CS').

Otherwise is not sargeable.
0
 
Russ SuterCommented:
Your method works OK. An alternative would be:

UPDATE
  Employees
SET
 Department = 'Customer Service'
WHERE
 RIGHT(mail_cd, 2) = 'CS'

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
ste5an - Does that mean that LIKE '%CS' is not SARGable, but LIKE 'CS%' is?  
If so, good to know, learn something new every day..
0
 
romsomAuthor Commented:
Thank you all!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your code.  -Jim
0
 
ste5anSenior DeveloperCommented:
@Jim: Yup, LIKE 'xyz%' can use an index seek.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.