January 29, 2003 at 5:29 pm
I need to convert all the columns in a table from upper case to title case. Does anyone know how to do this? I know Access has a format function but I haven't found a way to do this in SQL.
Thanks
Angela
January 30, 2003 at 2:40 am
There is no native method of doing this in SQL Server.
You could however create a DTS package which processed the data in your tables using the following VBScript command.
StrConv(strValue, vbProperCase)
January 30, 2003 at 4:00 am
When you say title case though do you mean proper or book title type? Just want to be sure what you are after.
January 30, 2003 at 11:13 pm
I am wanting to convert to Proper Case.
I was wanting to be able to do this via a stored procedure as I insert rows into a table. I am not overly familar with DTS so if I could keep it with everything else in my sp that would be ideal. not sure if this is possible though.
January 31, 2003 at 3:09 am
You could try something like this, which will curser through a table column, converting each row into title case, ie upper case the first letter of every word following a space (inc first word in string).
Replace @Col, @Table, and PrimaryKey as Applicable
But first, as you say, if all the data is already in Upper case, convert it all into lower case:
update @Table
set @col = lower(@col)
then:
declare @String varchar(8000)
declare @primarykey-2 Datatype -- insert correct datatype
declare @Space char(1)
declare @SpaceIndex int
set @Space = ' '
declare Title_Crs Cursor for
select @primarykey-2, @Col from @Table
open Title_Crs
fetch Next From Title_Crs Into @primarykey-2, @String
while (@@Fetch_Status = 0)
begin
set @SpaceIndex = charindex(@Space,@String)
-- set first word to title case
if @SpaceIndex <> 1
set @String = Stuff(
@String,
1,
1,
Upper(left(@String,1)))
-- set all subsequent words to title case
while @SpaceIndex <> 0
begin
set @String = Stuff(
@String,
@SpaceIndex + 1,
1,
Upper(substring(@String,@SpaceIndex + 1, 1)))
set @SpaceIndex = charindex(@Space,@String, @SpaceIndex + 1)
end
-- select @string
-- update the table
update @Table
set @Col = @String
where PrimaryKey = @primarykey-2
fetch Next From Title_Crs Into @primarykey-2, @String
end
close Title_Crs
deallocate Title_Crs
February 5, 2003 at 6:50 pm
Just for everyone's information:
You cannot use the VB or VBA function StrConv(strValue, vbProperCase) as suggested by paulhumpris as it is not supported by VBScript.
I ended up creating a User Defined Function to convert all my data to Proper Case.
CREATE FUNCTION fnTitleCase (@instring nvarchar(256))
RETURNS nvarchar(256)
AS
--This function will evaluate an input string and convert it to title-case format.
-- It uses the delimchars parameter to determine what are the triggering characters
-- to indicate capitalization of the subsequent character
BEGIN
DECLARE @strptr INT
DECLARE @outstring nvarchar(255)
DECLARE @strChar char(1)
DECLARE @delimchars nvarchar(256)
SET @outstring = ''
SET @strptr = 0
IF @delimchars is NULL
/* Plug in typical upper-case delimiters. */
/* NOTE: For localization purposes, you may wish to modify this */
SET @delimchars = ' ''-_.,'
/* Loop through the entire string */
WHILE @strPtr < len(RTRIM(@instring))
BEGIN
SET @strptr = @strptr + 1
SET @strchar = SUBSTRING(@instring,@strptr,1)
IF @strptr = 1 /* Assume that first character must always being
upper-cased*/
SET @outstring = UPPER(@strchar)
ELSE /*Check for other upper-case trigger character */
IF CHARINDEX(SUBSTRING(@instring,(@strptr - 1),1),@delimchars) > 0
-- SET @outstring = SUBSTRING(@outstring,1,@strptr)+UPPER(@strchar)
SET @outstring = @outstring+UPPER(@strchar)
ELSE
SET @outstring = @outstring+LOWER(@strchar)
END
RETURN @outstring
END
February 6, 2003 at 2:27 am
Apologies, I recall now that I used the StrConv function and created a VBComponent which I then called from VBScript.
It worked, but was slow. Sorry.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy