Solved

Anyway to simplified my query trim function?

Posted on 2011-03-17
2
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 70

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

624 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