Solved

Anyway to simplified my query trim function?

Posted on 2011-03-17
2
294 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 69

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

679 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