Solved

Anyway to simplified my query trim function?

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 160
Oracle - Stored Procedure Privilge access 3 39
data lookup in Oracle - need suggestions 55 104
Query Same ID From Multi-Record To One Record 4 27
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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

861 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

21 Experts available now in Live!

Get 1:1 Help Now