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.
| Author |
Topic |
|
Maquis
Starting Member
25 Posts |
 Posted - 2004-03-09 : 12:00:53
|
| Anybody already have a function to convert a string to proper case? either from all upper or all lower case (i.e., change 123 MAIN ST to 123 Main St) Thanks. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-09 : 12:52:49
|
| [code]USE NorthwindGOCREATE FUNCTION udf_UCaseFirst (@str varchar(8000), @Del char(1)=' ')RETURNS varchar(8000)ASBEGIN DECLARE @Words varchar(8000), @Pos INT, @x Int, @newstr varchar(8000), @Word varchar(100) SELECT @Words = '', @Pos = 1, @x = -1 SET @x = CHARINDEX(@Del, @str, @Pos) SELECT @Word = SUBSTRING(@str,1,@x-1) WHILE (@x <> 0) BEGIN SELECT @Words = @Words + ' '+ UPPER(SUBSTRING(@Word,1,1))+LOWER(SUBSTRING(@Word,2,LEN(@Word)-1)) SET @Pos = @x + 1 SET @x = CHARINDEX(@Del, @str, @Pos) IF @x <> 0 SELECT @Word = SUBSTRING(@str,@Pos,@x-@Pos) END SELECT @Word = REVERSE(SUBSTRING(REVERSE(@str),1,CHARINDEX(' ',REVERSE(@str),1)-1)) SELECT @Words = @Words + ' ' + UPPER(SUBSTRING(@Word,1,1))+LOWER(SUBSTRING(@Word,2,LEN(@Word)-1)) RETURN @WordsENDGOSELECT dbo.udf_UCaseFirst('123 MAIN STREET',' ')GODROP FUNCTION udf_UCaseFirstGO[/code]Brett8-) |
 |
|
|
Maquis
Starting Member
25 Posts |
Posted - 2004-03-09 : 13:01:59
|
| Thanks! I just added one thing - on the return, I did RETURN substring(@Words,2,len(@Words))to get rid of the extra leading blank space...Thanks so much! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-09 : 13:16:21
|
| See, that's me being a scrub...couldv'r used coalesce...And you can easily use ltrim...not sure if there a performance diff....Brett8-) |
|
|
|
|
|
|
|
|
function to convert string to proper case?