October 28, 2009 at 5:28 pm
Hi again,
Due to some bad data entry, I have a lot of lower case first letters that should be capitalized. Is there a function that will find the first letter of a word and capitalize it?
ex... State
ny
me
ky
Those should all start w/capital letter. Any advice would be great...
Thanks
October 28, 2009 at 5:44 pm
FOR JUST UPPER CASE
SELECT UPPER(column)
FROM table
http://www.brettb.com/SQL_Help_Change_Lower_Upper_Case.asp
FOR 1st LETTER UPPER CASE
SELECT
UPPER(LEFT,1))+
(RIGHT((column,len((column)-1))
FROM table
[font="Verdana"]-chira[/font]
October 28, 2009 at 5:50 pm
search for "ProperCase" in the scripts section for even more ways to do this;
here's an old example which assumes a Tally Table exists:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO
ALTER FUNCTION ProperCase(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
--first char is always capitalized?
CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
WHEN SUBSTRING(@OriginalText,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))
END
FROM dbo.Tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
select dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE;')
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply