SQLTeam.com | Weblogs | Forums

Only first letter is uppercase and set of words are uppercase in same column


#1

Hi All.
I need to convert case letter in the column data by way that only first letter will be uppercase and some words such as IP, OP, BC, UR, BD also will be uppercase. Is it possible to do? How to do?

Thanks.


#2

so you are saying if it is just two letter words make them all UPPER CASE otherwise only first letter is upper case?
what if it is a one letter word?


#3
create table #eugzl(eugzlogy varchar(250))

insert into #eugzl
select 'E' union all
select 'EU' union all
select 'EUG' union all
select 'EUGZ' union all
select 'EUgzl' union all
select 'EUGzl' union all
select 'EUGZl' union all
select 'EUGZL' 

select eugzlogy,
       case 
         when LEN(eugzlogy) > 2 then upper(LEFT(rtrim(ltrim(eugzlogy)),1)) + lower(SUBSTRING(eugzlogy,2,len(eugzlogy)))
		 else upper(rtrim(ltrim(eugzlogy))) 
		 end as shablam
  From  #eugzl

drop table #eugzl


#4

Hi yosiasz. Thanks for replay.

Each record in the column data that I would like to convert look like description sentence. And some of descriptions include abbreviations. The abbreviations have 2, 3, or 4 characters that I would like to keep in uppercase.

Thanks.


#5

Please provide sample data?


#6

sample of column data:

OP TOO LATE TO BILL
IP TO MANAGER FOR REVIEW
TO MEDICAL RECORDS FOR REVIEW
Paid in Full - 3rd Party Payment Accepted as Payment in Full
PAF TO THIRD PARTY INS. UNIT
"PATIENT UNCOOPERATIVE, TO B.D."
Fixed by UM

Base on that sample OP, IP, UM, PAF should be uppercase. A quotes will be good to remove in the record before last.

Thanks.


#7

please provide sample data as show in example

create table #eugzl(eugzlogy varchar(250))
insert into #eugzl

#8

In the previous post I show part of column data which I need to convert.


#9

What folk here want is a snippet of SQL with a CREATE TABLE for Temporary table and INSERT statements for your sample data.

Otherwise each person who wants to help you will individually have to create that test data to make an answer (or just write some code and hope it works which is a waste of both your time and ours) and ... usually no one will bother, its not like anyone is paying for advice here!!!

So pls create a sample data test rig in the style that @yosiasz has asked.