Converting to uppercase - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting to uppercase

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-10 : 18:00:40

Can u please help me with this query where one column of the table contains upper and lower cases and I want to update it to the first letter capital and then rest everything small letters

emp_test
MACHUNU
RAJAynsd
noissee
helYE

result should be
--------------
Machunu
Rajaynsd
Noissee
Helye

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-10 : 18:07:43

DECLARE @String varchar(10)

SET @String = 'TARA'

SELECT SUBSTRING(UPPER(@String), 1, 1) + LOWER(SUBSTRING(@String, 2, DATALENGTH(@String)))

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-10 : 18:08:22

I tried and I hope this should work.......
select UPPER(substring(emp_test,1,1))+LOWER(substring(emp_test,2,len(emp_test))) as emp_test from tbl_emps

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-10 : 18:19:06

Thanks Tara.... one more case is there where

DECLARE @String varchar(10)

SET @String = 'TARA.SQL'

Here the result should be Tara.Sql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-10 : 18:21:27

Then use PATINDEX to find the location of the period. You'll then want uppercase +1 of the position of the period.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-10 : 18:57:40

But how do I get the first string....part.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-10 : 19:00:01

Did you look up PATINDEX to see what it can do for you? It can find that period. So you'd then know where the first part and the second part is. So if the period is located at position 5. Then you'd know that the first part was 1 through (5-1). And the second part was (5+1) through DATALENGTH(@String).

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-10 : 19:57:20

There are some data which doesn't have period plus there are some where its length is just one so when U want to get the first part by doing charindex or patindex it gives an error..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-10 : 19:58:29

Please post an example of each type of data that you have.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-10 : 21:07:02

Here is some of the set of data I have .......

user_name
----------
DRErian.RETS
ravindra.mgr
em.hr
SQ
hr.
Sales1

select
SUBSTRING(UPPER(user_name),1,1)+ LOWER(SUBSTRING(user_name,2,CHARINDEX('.',user_name)-1))+
SUBSTRING(UPPER(user_name),CHARINDEX('.',user_name)+1,1)+ LOWER(SUBSTRING(user_name,CHARINDEX('.',user_name)+2,DATALENGTH(user_name)))

FROM
tbl_prof
WHERE
CHARINDEX('.', user_name, 1)>0

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-10 : 23:59:11

This query works..but don't know whether the where clause I have written finds everycase like this...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources