first letters of a string to upper case

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

General SQL Server Forums

Script Library

first letters of a string to upper case

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-04-23 : 07:07:39

Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional.
Thanks in advance for the help.

Php95saj

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-23 : 07:21:30

There's a couple of functions in T-SQL that you could use - UPPER and LOWER, to convert a string to uppercase or lowercase.
AFAIK there's no function that does what you want. If you're using SQL Server 2000, you could write your own function to convert the string to proper case.

php95saj
Starting Member

43 Posts

Posted - 2002-04-23 : 07:25:56

I am not so familiar with writing T-SQL plus I am on SQL Server 7.0.
Is there anything that could be done?

php95saj

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-23 : 07:32:16

There is a way you can do this in a view, but the code is very messy and I wouldn't recommend it. Besides, it can only cater for a set number of words.

Can you do the processing in the host language?

php95saj
Starting Member

43 Posts

Posted - 2002-04-23 : 07:43:55

I could, but I have recently started on ASP. I was on ColdFusion which has a very well supported set of RegEx and I could have done it.
But ASP is sh**e. SO I thought may be someone with more advanced knowledge of SQL would be able to help.
Thanks anyway.

php95saj

Nazim
A custom title

1408 Posts

Posted - 2002-04-23 : 08:59:50

you can try on these linds

declare @type varchar(30)
set @type='UNITED KINGDOM'
SELECT rtrim(SUBSTRING(UPPER(@type),1,1)+SUBSTRING(Lower(@type),2,charindex(' ',@type)-1))+' '+
substring(@type,(charindex(' ',@type)+1),1)+
SUBSTRING(Lower(@type),charindex(' ',@type)+2,len(@type))

HTH

--------------------------------------------------------------

Edited by - Nazim on 04/23/2002 09:02:06

php95saj
Starting Member

43 Posts

Posted - 2002-04-23 : 09:07:23

That works well, But only for (UNITED KINDOM). I think we need to check for any length of string? (may be 'UNITED STATES OF AMERICA' for example)?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-23 : 17:50:19

Any time you want to parse a string of undetermined length and undetermined number of values (words, or spaces, in your example) you can do this with a while loop, but that can really be a performance killer. You can also adapt the logic in Rob Volk's CSV Parsing Article.

In short, there is no convenient, built-in function to handle this.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-04-24 : 07:19:35

You can write a SQL Function.... As parameter you use the string. And it returns a string.

But better do it in the Application end and not in SQL.

In logic this is what you want:

Change the whole string in lower case ( LOWER(@string) ), than change everything in the string which has a space in te left in upper-case (e.g. ' u' to UPPER).

Use the keyword STUFF (you can find it in BOL)....

I tried to make a solution for a while, but a loop is indeed inevitable

. But considering you original post... Why not download it from some site??? Country code tables everywhere

.

Henri

~~~
SQL is nothing, writing it everything.

Edited by - henrikop on 04/24/2002 07:33:08

Nazim
A custom title

1408 Posts

Posted - 2002-04-24 : 09:52:49

i came up with this using Rob Volk's CSV Parsing Article(Thanx Mark).

Am sure this can be improved.

create table tally(id int)
declare @i int
set @i=1
while @i < 8001
begin
insert into tally(id) values(@i)
set @i=@i+1
end

create procedure ConvertProper(@mstring varchar(8000))
as
set nocount on

declare @m varchar(8000)
declare @mStr varchar(8000)
declare @si int

create table #quotes(phrase varchar(8000))
insert into #quotes values(@mString)

SELECT identity(int,1,1) as no,NullIf(SubString(' ' + Phrase + ' ' , ID , CharIndex(' ' , ' ' + Phrase + ' ' , ID) - ID) ,
'') AS Word into #test
FROM Tally, #Quotes
WHERE ID <= Len(' ' + Phrase + ' ') AND SubString(' ' + Phrase + ' ' , ID - 1, 1) = ' '
AND CharIndex(' ' , ' ' + Phrase + ' ' , ID) - ID > 0

set @m=''
set @mStr=''
while Exists (select * from #test)
begin
select @si=min(no) from #test

select @m=word from #test where no=@si
set @mStr=@mStr+rtrim(SUBSTRING(UPPER(@m),1,1)+SUBSTRING(Lower(@m),2,len(@m)))+' '

delete from #test where no=@si
end
select @mStr as Word

go

Usage:
exec ConvertProper 'UNITED STATES OF AMERICA'

HTH

--------------------------------------------------------------

Edited by - Nazim on 04/28/2002 01:03:29

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-24 : 11:00:03

ASP is not sh**e .. just like any programming language ... it's only as powerful as the imagination and intuition of the programmer using it... ASP does have regular expression support through the RegExp object...

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-04-24 : 11:31:33

A quick search on www.4guysfromrolla.com led to a link on another site which led this already written VBscript function to convert a string to "proper" case.

http://www.asp101.com/samples/viewasp.asp?file=pcase%2Easp

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-04-24 : 13:09:10

quote:

A quick search on www.4guysfromrolla.com led to a link on another site which led this already written VBscript function to convert a string to "proper" case.

http://www.asp101.com/samples/viewasp.asp?file=pcase%2Easp

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Hmm...isn't there are built-in function in VBScript for this?

--
HTH,
Vyas
Check out my SQL Server site @
http://vyaskn.tripod.com

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-04-24 : 13:10:27

It's more efficient to do this in the front-end. However, if you want to do this using T-SQL, I have a User Defined Function (UDF) and a stored proc for this, at my site: http://vyaskn.tripod.com/code.htm#propercase

--
HTH,
Vyas
Check out my SQL Server site @
http://vyaskn.tripod.com

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-24 : 18:03:04

php95saj, because you are using SQL 7, you cannot use a function (UDF). Thankfully VyasKN has a stored procedure version for SQL 7 at the link previeously posted. Or, Nazim's proc. Either way, calling a sproc is not as neatly handled in a SELECT or Update statement as UDF's are in SQL 2000, so a little experimentation may be required to come up with the proper method to use these.

You may find this is simpler to handle on the ASP side for display purposes instead of actually changing the data in SQL Server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources