Solved

Anyway to simplified my query trim function?

Posted on 2011-03-17
2
261 Views
Last Modified: 2012-05-11
I want to use ltrim(rtrim(degree)) instead of just degree in the query.  Is there a way that I don't have to repeat these trim syntax everywhere?  Thanks.
Update dbo.PersonDegree
	SET degree = 
	case 
		when (degree='M.D.', 'MD, CM', 'MD BS', 'MB, BCh', 'MB, BS', 'M.D./PhD.', 'MB, Ch.B.') then 'MD'
		when (degree in('B.Sc.', 'B.S.', 'B.S', 'BS(High Honors)', 'BS/MS', 'B.sc.', 'BSc')) then 'BS'
		when (degree IN('Ph. D.', 'Ph D', 'Ph.D.', 'Ph.D', 'PhD.')) then 'PhD.'
		when (degree IN('M.S.', 'Masters', 'M. Sci.')) then 'MS'
		when degree='DMD' then 'DDM'
		when degree ='Psy.D' then 'DPSY'
		when degree in('B.A', 'B.A.') then 'BA'
		when degree='B.M.S.' then 'BMS'
		when degree='D.O.' then 'DO'
		when degree='DNSC' then 'DSN'
		when degree='M.A.' then 'MA'
		when degree='M.B.B.S' then 'MD'
		when degree='MS.B' then 'MSB'
		when degree='P.A.' then 'PA'
		when degree='MSN BS' then 'MSN'
		when degree='M. Phil.' then 'MS'
		when degree in('MS Ed', 'MS Ed.') then 'MSEd'
		ELSE degree
	End

Open in new window

0
Comment
Question by:lapucca
2 Comments
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 35158538
You can use a derived table:
Update PD
set degree =
  case 
    when (tdegree='M.D.', 'MD, CM', 'MD BS', 'MB, BCh', 'MB, BS', 'M.D./PhD.', 'MB, Ch.B.') then 'MD'
    when (tdegree in('B.Sc.', 'B.S.', 'B.S', 'BS(High Honors)', 'BS/MS', 'B.sc.', 'BSc')) then 'BS'
    when (tdegree IN('Ph. D.', 'Ph D', 'Ph.D.', 'Ph.D', 'PhD.')) then 'PhD.'
    when (tdegree IN('M.S.', 'Masters', 'M. Sci.')) then 'MS'
    when tdegree='DMD' then 'DDM'
    when tdegree ='Psy.D' then 'DPSY'
    when tdegree in('B.A', 'B.A.') then 'BA'
    when tdegree='B.M.S.' then 'BMS'
    when tdegree='D.O.' then 'DO'
    when tdegree='DNSC' then 'DSN'
    when tdegree='M.A.' then 'MA'
    when tdegree='M.B.B.S' then 'MD'
    when tdegree='MS.B' then 'MSB'
    when tdegree='P.A.' then 'PA'
    when tdegree='MSN BS' then 'MSN'
    when tdegree='M. Phil.' then 'MS'
    when tdegree in('MS Ed', 'MS Ed.') then 'MSEd'
    ELSE tdegree
  End
from (select *, ltrim(rtrim(degree)) as tdegree from dbo.PersonDegree) PD

Open in new window

0
 

Author Closing Comment

by:lapucca
ID: 35158667
Cool!  Thanks.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now