March 11, 2005 at 12:57 pm
I am looking to write a single query to update 1 field from all upper case to Mixed case text. ie ( SAN FRANCISCO to San Francisco). I can be assured that the words are separated by 1 space
Can anyone help?
March 11, 2005 at 1:17 pm
Here's a UDF that I found sometime back. It assumes the First letter of the string and every letter after a space should be capitalized. All else is set to lowercase. I haven't tested it for oddball situations, so take it for what it's worth.
CREATE FUNCTION dbo.udfProperName(@sysName sysname)
RETURNS sysname
AS
BEGIN
DECLARE @intLen int,
@intI int,
@sysOutname sysname,
@bitLastSpc bit
SET @intLen = DATALENGTH(@sysName)
SET @intI = 1
SET @bitLastSpc = 1
SET @sysOutname = ''
WHILE @intI < @intLen BEGIN
SET @sysOutname = @sysOutname +
CASE @bitLastSpc
WHEN 1 THEN UPPER(SUBSTRING(@sysName, @intI, 1))
ELSE LOWER(SUBSTRING(@sysName, @intI, 1))
END
SET @bitLastSpc = CASE SUBSTRING(@sysName, @intI, 1) WHEN ' ' THEN 1 ELSE 0 END
SET @intI = @intI + 1
END
RETURN(@sysOutname)
END
Mark
March 11, 2005 at 1:58 pm
This is another variant:
create function dbo.udfCamelCase(@strin varchar(500))
returns varchar(500)
AS
begin
declare @copy varchar(500), @i int, @len int
set @copy = lower(ltrim(rtrim(@strin)))
select @copy = upper(left(@copy,1)) + substring(@copy,2, len(@copy)-1), @len = len(@copy)
set @i = charindex(' ',@copy,1)
while (@i < @len-1) and @i <> 0
begin
set @copy = stuff(@copy,@i+1,1,upper(substring(@copy,@i+1,1)))
set @i = charindex(' ',@copy,@i+1)
end
return(@copy)
end
then
select dbo.udfCamelCase( YOURCOLUMN ) from YOURTABLE
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply