T-Sql function to convert a varchar - in this inst

Question

Does anyone have in their back pocket a function that can achieve this?

Answer 1

If you really want to do this in T-SQL and without a loop, see Tony Rogerson's article "Turning stuff into "Camel Case" without loops"

I haven't tried it... that's what client code it for :-)

Answer 2

Will any given row only contain a firstname or a lastname that you wish to convert or will it contain full names separated by spaces? Also, are there any other rules you wish to what characters it should "upper" or lower"?

If you can guarantee that it's only first and last names and you aren't dealing with any specialized capitalization such as after an apostrophe, might this do what you're looking for?

    SELECT  -- Initial of First Name
        UPPER(LEFT(FullName, 1))
        -- Rest of First Name
        + SUBSTRING(LOWER(FullName), 2, CHARINDEX(' ', FullName, 0) - 2) 
        -- Space between names
        + ' ' 
        -- Inital of last name
        + UPPER(SUBSTRING(FullName, CHARINDEX(' ', FullName, 0) + 1, 1)) 
        -- Rest of last name
        + SUBSTRING(LOWER(FullName), CHARINDEX(' ', FullName, 0) + 2, LEN(FullName) - CHARINDEX(' ', FullName, 0) + 2)
FROM Employee

Answer 3

No cursors, no while loops, no (inline) sub-queries

-- ===== IF YOU DON'T HAVE A NUMBERS TABLE =================
--CREATE TABLE Numbers (
--   Num INT NOT NULL PRIMARY KEY CLUSTERED WITH(FILLFACTOR = 100)
--)
--INSERT INTO Numbers
--SELECT TOP(11000)
--   ROW_NUMBER() OVER (ORDER BY (SELECT 1))
--FROM master.sys.all_columns a
--   CROSS JOIN master.sys.all_columns b

DECLARE @text VARCHAR(8000) = 'my text to make title-case';
DECLARE @result VARCHAR(8000);

SET @result = UPPER(LEFT(@text, 1));

SELECT
   @result += 
      CASE 
      WHEN SUBSTRING(@text, Num - 1, 1) IN (' ', '-') THEN UPPER(SUBSTRING(@text, Num, 1)) 
      ELSE SUBSTRING(@text, Num, 1) 
      END
FROM Numbers
WHERE Num > 1 AND Num <= LEN(@text);

PRINT @result;

Answer 4

This kind of function is better done on the application side, as it will perform relatively poorly in SQL.

With SQL-Server 2005 and above you could write a CLR function that does that and call it from your SQL. Here is an article on how to do this.

Answer 5

Found this here :-

create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
   declare @Reset bit;
   declare @Ret varchar(8000);
   declare @i int;
   declare @c char(1);

   select @Reset = 1, @i=1, @Ret = '';

   while (@i <= len(@Text))
    select @c= substring(@Text,@i,1),
               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
               @i = @i +1
   return @Ret
end

Results from this:-

select dbo.propercase('ALL UPPERCASE');  -- All Uppercase
select dbo.propercase('MiXeD CaSe'); -- Mixed Case
select dbo.propercase('lower case'); -- Lower Case
select dbo.propercase('names with apostrophe - mr o''reilly  '); -- Names With Apostrophe - Mr O'Reilly
select dbo.propercase('names with hyphen - mary two-barrels  '); -- Names With Hyphen - Mary Two-Barrels

Answer 6

I'd do this outside of TSQL, in the calling code tbh. e.g. if you're using .NET, it's just a case of using TextInfo.ToTitleCase.

That way, you leave your formatting code outside of TSQL (standard "let the caller decide how to use/format the data" approach).