Converting upper case to "Proper Case" in SQL

[MaffewW]

Dear All

I have an address field as so

1 BEAR ROAD
3 LONDON STREET
....

In excel you can you the 'proper' function to change the case to

1 Bear Road
3 London Street

Is there a SQL equivalent? I know I could use RTRIM with LOWER, but could I get anything to work on a field where there are multiple words of different length?

Thanks

Matt

 

[bperry]

There is no equivalent in SQL Server. We basically have to 'roll our own'.

If SQL Server 2000, then you can write a UDF (User Defined Function) to do this. If SQL 7 of less, then a stored procedure will be required.

I have seen the code to do this. It involves existing SQL functions like upper(), lower(), rtrim(), len(), etc

You can write it yourself, or someone on this forum can prob find it for you (if you indicate which version you use)

bp

 

[MaffewW]

bp, thanks I didnt think that there was a specific function to do this.

I'm using SQL Server 7, if anyone has the relevant code

 

[mukund]

Hi MaffewW,
In such case you can think of a front end tool that has the proper function like VB, Use that to retrieve, convert the address string to proper case and update the addres string back in the DB.

Mukund.

 

[tlbroadbent]

Check the following links for ideas and examples.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14117

http://vyaskn.tripod.com/code/propercase_proc.txt

For future reference: SQL 2000 UDF

http://vyaskn.tripod.com/code/propercase.txt

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MaffewW]

Thanks very much for the information guys

Mukund, we kind of already do that. I've got an Access Project with exports a stored procedure to a csv file, and run a macro at the Excel end to due the formatting. I was trying to get the SQL in the stored proc to eliminate the extra step.

 

[rajeessh]

pl use the following stored procedure

create procedure sp_capitalize
@nstrconvert nvarchar(2000)
as
declare @address nvarchar(2000)
declare @toconvert nvarchar(1),@spaceat int
set @address = lower(@nstrconvert)
set @spaceat = 0
while @spaceat <> len(@address)
begin
select @spaceat = charindex(' ',@address,@spaceat+1)
if (@spaceat = 0 or @spaceat = len(@address)) break
select @toconvert = substring(@address,@spaceat+1,1)
if ascii(@toconvert) >= 97 and ascii(@toconvert) <= 122
begin
select @address = substring(@address,1,@spaceat-1) + ' ' + char(ascii(@toconvert) - 32) + substring(@address,@spaceat+2,len(@address) - @spaceat)
end
end
select @address



then u can call as sp_capitalize '3 BASIC SAMPLE'

this will display as 3 Basic Sample