Proper case - 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)

Proper case

Author

Topic

vwilsonjr
Starting Member

45 Posts

Posted - 2002-12-02 : 15:59:26

What is the correct syntax to change BOB SMITH to Bob Smith. Also how would I do street address for example 123 MAIN STREET to 123 Main Street

I know it is simply but just getting a brain cramp

Thanks

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-02 : 16:51:25

This might help.

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=15152

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-02 : 17:23:34

As always, i find the simpliest answer to be the best. The links have some solutions, but they are LOOOOONG and overly complex.

Try:

CREATE FUNCTION fn_Test (@String varchar(8000))
RETURNS varchar(8000) AS

BEGIN
declare @DelimiterChars varchar(200);
declare @temp int;
set @String = ' ' + LOWER(@String);
set @temp = 1
set @DelimiterChars = ',. ';

WHILE @temp <= Len(@String) BEGIN
if CHARINDEX(substring(@String,@temp-1,1),@DelimiterChars) <> 0
SET @String = Left(@String,@temp-1) + UPPER(SUBSTRING(@String,@temp,1)) + SUBSTRING(@String,@temp+1,8000);
SET @temp = @temp + 1;
END
RETURN substring(@String,2,8000);

END

Works pretty well for me, and should work for all cases. The 'DelimiterChars' string you can edit; it determines which characters signal the start of a new word to capitalize.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-02 : 17:27:13

There's one in the middle of this one:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10107

...the nested REPLACE functions. Might be a little faster than a UDF, but do tests on both methods before you decide.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-02 : 17:43:55

I think the main drawback is only letters preceded by a space will be capitalized, as opposed to other characters like '.' or ','.

Not sure which is faster, that'd be interesting to find out. it depends on how well compiled user-defined functions are, in relation to built-in SQL server functions.

I have found keeping a UDF like this in my standard 'library' to be quite useful.

Have fun!

- Jeff

vwilsonjr
Starting Member

45 Posts

Posted - 2002-12-03 : 13:09:05

quote:

As always, i find the simpliest answer to be the best. The links have some solutions, but they are LOOOOONG and overly complex.

Try:

CREATE FUNCTION fn_Test (@String varchar(8000))
RETURNS varchar(8000) AS

BEGIN
declare @DelimiterChars varchar(200);
declare @temp int;
set @String = ' ' + LOWER(@String);
set @temp = 1
set @DelimiterChars = ',. ';

WHILE @temp <= Len(@String) BEGIN
if CHARINDEX(substring(@String,@temp-1,1),@DelimiterChars) <> 0
SET @String = Left(@String,@temp-1) + UPPER(SUBSTRING(@String,@temp,1)) + SUBSTRING(@String,@temp+1,8000);
SET @temp = @temp + 1;
END
RETURN substring(@String,2,8000);

END

Works pretty well for me, and should work for all cases. The 'DelimiterChars' string you can edit; it determines which characters signal the start of a new word to capitalize.

- Jeff

Jeff thanks for the help. I'll admit I'm a newbie with this. This will be a dumb question but here it goes. Where would I put your code. Currently I have the following code which takes data from a temp table that is all uppercase and insert into a live table that I would not like to have all uppercase

Thanks for you help.

insert into appuser
(forename,
surname,
Job_Title,
Employment_Start,
Office_Phone,
home_address,
Home_Phone,
Email_Address,
Emergency_Contact,
Employee_Site_ID,
User_Type,
DOB,
Sex,
UserName,
Password,
Employee_Number,
Employment_End,
Category_ID,
Mobile_Phone,
Creation_Date,
Creation_User,
LastUpdate_Date,
LastUpdate_User)
/**/
select

givenname,
surname,
JobTitle,
DateStarted,
'(' + rtrim(OfficeAreaCode)+ ') ' + OfficePhone as Office_Phone,
address1 + CHAR(13) + char(10)+
address2 + CHAR(13) + char(10)+
rtrim(City) + ', ' + State + PostalCode
as Home_Address ,
'('+rtrim(HomeAreaCode)+') ' + ltrim(HomePhone) as Home_Phone,
Email,
Emergency1 + CHAR(13) + char(10)+
Emergency2 + CHAR(13) + char(10)+
as Emergency_contact,
SiteID,
'E' as User_Type,
DOB,
Gender,
AddressNumber,/* This is used to fix bug requiring Username in vs 2.7.3 */
'!@#$%^&*()_+"?><,./;l' as password,
ltrim(AddressNumber) as Employee_Number,
DateTerminated,
Region,
'(' + rtrim(MobileAreaCode)+') ' + ltrim(MobilePhone) as Mobile_Phone,
getdate() as creation_date,
'JDE Upload' as Creation_User,
getdate() as LastUpdate_Date,
'JDE Upload' as LastUpdate_User
from AppuserTemp

where ltrim(AddressNumber) not in
(select Employee_Number from appuser where Employee_Number is not null)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-03 : 14:21:26

First, take the code I posted and paste it into the query analyzer. Make sure you are connected to the same database that your data lives in.

Rename the "CREATE FUNCTION fn_Test(...)" part to something like "CREATE FUNCTION ProperCase(...)", or whatever you would like the function to be called.

Then, execute the code from the query analyzer. When it completes, a new function will appear in the User-Defined Functions part of your database, called "ProperCase".

This funciton is just like CHAR() or RTRIM() or any other function you have already shown that you know how to use. You just need to reference it like this:

SELECT dbo.ProperCase(fieldname) as NewField FROM Table

- Jeff

vwilsonjr
Starting Member

45 Posts

Posted - 2002-12-03 : 21:15:43

Thanks for your help Jeff. I'll give that a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources