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