SQL Server: Make All Upper Case to Proper Case/Title Case

SQL Server: Make all UPPER case to Proper Case/Title Case

Here's a UDF that will do the trick...

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

  if @Text is null
    return null;

  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

You will still have to use it to update your data though.

Change lower case to upper (title) case using sql query

There's no such function in any database which do this for you. You've to write a function which actually performs the check on each word in a sentence. Please check the solutions below:

MySql:

DELIMITER //

    CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

    RETURNS VARCHAR(255)

    DETERMINISTIC

    BEGIN
        DECLARE len INT;
        DECLARE i INT;

        SET len   = CHAR_LENGTH(input);
        SET input = LOWER(input);
        SET i = 0;

        WHILE (i < len) DO
            IF (MID(input,i,1) = ' ' OR i = 0) THEN
                IF (i < len) THEN
                    SET input = CONCAT(
                        LEFT(input,i),
                        UPPER(MID(input,i + 1,1)),
                        RIGHT(input,len - i - 1)
                    );
                END IF;
            END IF;
            SET i = i + 1;
        END WHILE;

        RETURN input;
    END//

DELIMITER ;

Example:

SELECT CAP_FIRST('this is exACtly tHe same!')

Output:

This Is Exactly The Same!

Copyrights:

http://joezack.com/2008/10/20/mysql-capitalize-function/

Hope this helps!

Change the case on Mc surnames to proper case

This can be a slippery slope. You will find many exceptions. That said, here is a scaled down version of my proper function.

Example

Select [dbo].[svf-Str-Proper]('john old macdonald ii phd,dds llc')

Reurns

(No column name)
John Old MacDonald II PhD,DDS LLC

The Function if Desired

CREATE FUNCTION [dbo].[svf-Str-Proper] (@S varchar(max))
Returns varchar(max)
As
Begin
    Set @S = ' '+replace(replace(Lower(@S),'   ',' '),'  ',' ')+' '
    ;with cte1 as (Select * From (Values(' '),('-'),('/'),('\'),('['),('{'),('('),('.'),(','),('&'),(' Mc'),(' Mac'),(' O''') ) A(P))
         ,cte2 as (Select * From (Values('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M')
                                       ,('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
                                       ,('LLC'),('PhD'),('MD'),('DDS')
                                       ,('II '),('III '),('IV ')
                                       ,('ID '),('PW ')
                                 ) A(S))
         ,cte3 as (Select F = Lower(A.P+B.S),T = A.P+B.S From cte1 A Cross Join cte2 B 
                   Union All 
                   Select F = Lower(B.S+A.P),T = B.S+A.P From cte1 A Cross Join cte2 B where A.P in ('&') 
                  ) 
    Select @S = replace(@S,F,T) From cte3 
    Return rtrim(ltrim(@S))
End

Convert proper case to space after proper case - SQL Server

Try this It Worked

Create Function dbo.Split_On_Upper_Case(@Temp VarChar(100))
Returns VarChar(100)
AS
Begin

Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^ ][A-Z]%'
While PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues collate Latin1_General_Bin, @Temp) + 1, 0, ' ')

Return @Temp
End

Basic proper case string in generic SQL

You can use CONCAT and concatenate part of string.

CREATE TABLE tab(x VARCHAR(1000));

INSERT INTO tab VALUES ('the sky is GRAY');

SELECT CONCAT(UPPER(LEFT(x, 1)), LOWER(RIGHT(x, LENGTH(x) - 1))) AS result
FROM tab;

SqlFiddleDemo

For more secure solution I would also trim text, because:

INSERT INTO tab VALUES ( '   the sky is GRAY');

You will get:

    the sky is gray

With trim:

SELECT CONCAT(UPPER(LEFT(TRIM(x), 1)), 
              LOWER(RIGHT(TRIM(x), LENGTH(TRIM(x)) - 1))) AS result
FROM tab;

SqlFiddleDemo2

Warning:

I highly doubt there's one query to "rule them all." Depending on your RDBMS, you may need to use:

• SUBSTRING instead of LEFT/RIGHT
• LTRIM(RTRIM) instead of TRIM
• LEN/DATALENGTH instead of LENGTH
• +/|| instead of CONCAT

SQL Proper Case Function Query Refinement

I would do it differently:

CREATE FUNCTION [dbo].[f_ProperCase]
(@Text as varchar(80))
RETURNS varchar(80) as
BEGIN

DECLARE @Reset bit
DECLARE @Ret varchar(80)
DECLARE @i int
DECLARE @c char(1)
DECLARE @Text1 varchar(81)

SELECT @Reset = 1, @i=1, @Ret = '', @Text1 = ' ' + @Text

WHILE @i <= LEN(@Text1)
    SELECT @c= SUBSTRING(@Text1,@i,1),
    @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
    @Reset= CASE WHEN 
    CASE WHEN SUBSTRING(@Text1,@i-2,3) like ' [DdOoLl]''' THEN 1 
    WHEN SUBSTRING(@Text1,@i-2,4) like ' [Mm][cC][a-zA-Z]' THEN 1 
    WHEN SUBSTRING(@Text1,@i-3,5) like ' [Mm][Aa][cC][a-zA-Z]' THEN 1 
    ELSE 0 
    END = 1 
    THEN 1 
    ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0 
    ELSE 1 
    END 
    END,
    @i = @i +1
RETURN stuff(@Ret, 1, 1, '')
end

This function supports O', L', D', as well as Mc, and Mac. The function is also converts from any case (not only the upper case) to the proper case

SQL Fiddle

select dbo.f_ProperCase('CORMACK')
      ,dbo.f_ProperCase('Mcdonald ronald')
      ,dbo.f_ProperCase('o''hara')

|         |                 |        |
|---------|-----------------|--------|
| Cormack | McDonald Ronald | O'Hara |

SQL: capitalize first letter only

Are you asking for renaming column itself or capitalise the data inside column? If its data you've to change, then use this:

UPDATE [yourtable]
SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word)))

If you just wanted to change it only for displaying and do not need the actual data in table to change:

SELECT UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word))) FROM [yourtable]

Hope this helps.

EDIT: I realised about the '-' so here is my attempt to solve this problem in a function.

CREATE FUNCTION [dbo].[CapitalizeFirstLetter]
(
--string need to format
@string VARCHAR(200)--increase the variable size depending on your needs.
)
RETURNS VARCHAR(200)
AS

BEGIN
--Declare Variables
DECLARE @Index INT,
@ResultString VARCHAR(200)--result string size should equal to the @string variable size
--Initialize the variables
SET @Index = 1
SET @ResultString = ''
--Run the Loop until END of the string

WHILE (@Index BEGIN
IF (@Index = 1)--first letter of the string
BEGIN
--make the first letter capital
SET @ResultString =
@ResultString + UPPER(SUBSTRING(@string, @Index, 1))
SET @Index = @Index+ 1--increase the index
END

-- IF the previous character is space or '-' or next character is '-'

ELSE IF ((SUBSTRING(@string, @Index-1, 1) =' 'or SUBSTRING(@string, @Index-1, 1) ='-' or SUBSTRING(@string, @Index+1, 1) ='-') and @Index+1 <> LEN(@string))
BEGIN
--make the letter capital
SET
@ResultString = @ResultString + UPPER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--increase the index
END
ELSE-- all others
BEGIN
-- make the letter simple
SET
@ResultString = @ResultString + LOWER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--incerase the index
END
END--END of the loop

IF (@@ERROR
<> 0)-- any error occur return the sEND string
BEGIN
SET
@ResultString = @string
END
-- IF no error found return the new string
RETURN @ResultString
END

So then the code would be:

UPDATE [yourtable]
SET word=dbo.CapitalizeFirstLetter([STRING TO GO HERE])

---

Related Topics