July 19, 2006 at 2:47 pm
Hi experts,
There is any way in T-sql to change the case of a string, it doesn't matter how it is written I'd like to have the first letter capitalized.
Example:
this is my example
Replaced:
This Is My Example
Any help to handle this matter will be really appreciated.
CC
July 19, 2006 at 3:09 pm
Select upper(Left(@str,1)) + substring(str,2,Len(@str)-1)
* Noel
July 19, 2006 at 4:20 pm
Thanks noeld, unfortunately I'm looking for a method for capitalizing all words, this works only for the first word.
July 19, 2006 at 4:31 pm
July 20, 2006 at 7:53 am
you can also try here:
http://www.bigbold.com/snippets/posts/show/709
lotsa other good snippets on this page, I may need to add it to my bag o' tricks...
July 20, 2006 at 10:48 am
Pam/Mike thanks this is great!
July 20, 2006 at 6:24 pm
Thought I'd throw one of my own into the circle...
If you don't have a "Tally" or "Numbers" table, now is as good a time as any to make one. A Tally table is nothing more than a table with a single column of numbers with a clustered primary key to cover the column. It has many uses and serves functions, like a ProperCase function, very well. It has some pretty slick date related applications, as well. It also makes doing things like this an absolute breeze.
Here's how to make the Tally table...
--===== Create a permanent table of numbers.
SELECT TOP 9999
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM dbo.SYSCOLUMNS sc1,
dbo.SYSCOLUMNS sc2
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N)
GRANT REFERENCES, SELECT ON dbo.Tally TO PUBLIC
...and here's the proper case function code...
CREATE FUNCTION dbo.fProperCase (@MyString VARCHAR(8000))
/******************************************************************************
Purpose:
This function takes the input string, changes all characters to lower case,
and then changes the leading character of each word to uppercase.
Dependencies:
The dbo.Tally table (contains a column of numbers from 1 to 9999) must
exist prior to use of this function.
Revision History:
10/23/2005 - Jeff Moden - Initial creation and unit test
******************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
--===== First, set the whole string to lowercase so we know the condition
SET @MyString = LOWER(@MyString)
--===== Set the first character to uppercase, no matter what
SET @MyString = STUFF(@MyString,1,1,UPPER(SUBSTRING(@MyString,1,1)))
--===== Set the first character following a "separator" to uppercase
SELECT @MyString = STUFF(@MyString,N+1,1,UPPER(SUBSTRING(@MyString,N+1,1)))
FROM dbo.Tally
WHERE N<LEN(@MyString)
AND SUBSTRING(@MyString,N,1) NOT LIKE '%[A-Z]%'
--===== Return the proper case value
RETURN @MySTRING
END
GO
...and here's an example of usage...
SELECT dbo.fProperCase('noW IS The time fOr AlL good men to come to the aid of their country.')
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply