CODEWITHSUNDEEP
sql-servert-sql
AJM
AJM

Reputation: 32490

T-Sql function to convert a varchar - in this instance someone's name - from upper to title case?

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

Upvotes: 5

Views: 1128

Answers (6)

Florian Reischl
Florian Reischl

Reputation: 3856

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;

Upvotes: 1

crosan
crosan

Reputation: 486

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

Upvotes: 0

gbn
gbn

Reputation: 432271

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 :-)

Upvotes: 1

Andy Robinson
Andy Robinson

Reputation: 7439

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

Upvotes: 3

AdaTheDev
AdaTheDev

Reputation: 147244

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).

Upvotes: 2

Oded
Oded

Reputation: 499062

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.

Upvotes: 1

Related Questions