Built-in function to capitalise the first letter of each word

Question

I don't want to create a custom function for that if such function already exists in SQL Server.

Input string: This is my string to convert
Expected output: This Is My String To Convert

Hemimorphite

Technically, that would be PascalCase. – Chasseur

@thomas, technically it is neither, its just a string of capitalized words – Rabblement

@nathan gonzalez - I stand corrected. – Chasseur

Answer 1

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(4000)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
        SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))

    SET @Index = @Index + 1
END

RETURN @OutputString

END



Declare @str nvarchar(100)
SET @str = 'my string to convert'
SELECT @str = [dbo].[InitCap](@str)
SELECT @str 

Durward

Answer 2

AFAIK, SQL Server has no built-in function for this.
You have to write custom function for it.

Try this.

CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END  

Output :

Input String    : 'microSoft sql server'
Output String   : 'Microsoft Sql Server'

Casandra

Nice, but only handles spaces as delimiters. Will not handle Dashes/Hyphens for example, but still answers the the question. Will also not handle NULL, returns an empty-string instead. – Copepod

Also doesn't handle single letter strings (ie First Middle Last names as separate strings): dbo.CamelCase(p.FirstName), dbo.CamelCase(p.LastName), dbo.CamelCase(p.MiddleName) – Pyroxylin

Answer 3

I'd have to go with "No, that does not exist". This based on several years of perusing the available string-functions in T-SQL and some pretty recent 5-day courses in SQL Server 2008 R2.

Of course, I still could be wrong :).

Outbreak

Answer 4

If the goal of your operation is to prettify strings of Names then proper capitalization could be defined as the first letter of each word separated by non-alphabet characters.

Other solutions do not take into account:

1. Preserving spacing (especially trailing spaces).
2. Preserving NULL, empty-string, or a string of just spaces.
3. Handling more than just spaces (e.g. dashes, commas, underscores, etc...)
4. Handling more than one non-alpha character between words/tokens.
5. Handling exceptions (e.g. McDonald or III like in "James William Bottomtooth the III").

Note: My solution does not handle exceptions.
If you are very concerned about those, then I suggest writing a CLR C# assembly for those as it will be tricky, and strings are an area where C# excels.
Another solution on here tries to account for this, but it would still take "ivan terrible the iv" and output "**IV***an Terrible The IV*".

This is the function I came up with:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fs_PascalCase]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fs_PascalCase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fs_PascalCase]
(
    @Text nVarChar(MAX)
)
RETURNS nVarChar(MAX)
AS
BEGIN
        SET @Text = LOWER(@Text)--This step is optional.  Keep if you want the code below to control all casing. - 11/26/2013 - MCR.
    DECLARE @New nVarChar(MAX) = (CASE WHEN @Text IS NULL THEN NULL ELSE '' END)--Still return null when source is null. - 11/26/2013 - MCR.
    DECLARE @Len   Int = LEN(REPLACE(@Text, ' ', '_'))--If you want to count/keep trailing-spaces, you MUST use this!!! - 11/26/2013 - MCR.
    DECLARE @Index Int = 1--Sql-Server is 1-based, not 0-based.
    WHILE (@Index <= @Len)
        IF (SUBSTRING(@Text, @Index, 1) LIKE '[^a-z]' AND @Index + 1 <= @Len)--If not alpha and there are more character(s).
            SELECT @New = @New + UPPER(SUBSTRING(@Text, @Index, 2)), @Index = @Index + 2
        ELSE
            SELECT @New = @New +       SUBSTRING(@Text, @Index, 1) , @Index = @Index + 1

    --If @Text is null, then @Len will be Null, and everything will be null.
    --If @Text is '',   then (@Len - 1) will be -1, so ABS() it to use 1 instead, which will still return ''.
    RETURN ( UPPER(LEFT(@New, 1)) + RIGHT(@New, ABS(@Len - 1)) )
END
GO

You would call it like so:

SELECT dbo.fs_PascalCase(NULL)[Null],
       dbo.fs_PascalCase('')[EmptyString],
       dbo.fs_PascalCase('hello   how are-you TODAY    ')[LongString]

The output will look like this:

Copepod

Answer 5

My Strategy

• If the name is already in mixed case, trust that it’s right.
• If the name is not in mixed case, then do the following:
• Trim up the name to eliminate white space
• Account for the names that start with “Mc” like “McDavid”
• Account for names with apostrophes like O’Reilly
• Account for hyphenated names (married names) “Anderson-Johnson”
• Account for multiple word names like “La Russa”
• Make sure suffixes included in the names field are capitalized appropriately

The Code

Here's my original post on this: Converting String to Camel Case in SQL Server

CREATE FUNCTION [dbo].[GetCamelCaseName]
(
    @Name varchar(50)
)
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN
    -- Declare the return variable here
    DECLARE @NameCamelCase VARCHAR(50)  

    -- This is determining whether or not the name is in camel case already (if the 1st character is uppercase
    -- and the third is lower (because the 2nd could be an apostrophe).  To do this, you have to cast the 
    -- character as varbinary and compare it with the upper case of the character cast as varbinary.  

    IF (CAST(SUBSTRING(@Name, 1,1) as varbinary) = CAST(SUBSTRING(UPPER(@Name), 1, 1) as varbinary)         
            AND ((CAST(SUBSTRING(@Name, 2,1) as varbinary) = CAST(SUBSTRING(LOWER(@Name), 2, 1) as varbinary)
                    AND SUBSTRING(@Name, 2,1) != '''')
                or
                (CAST(SUBSTRING(@Name, 4,1) as varbinary) = CAST(SUBSTRING(LOWER(@Name), 4, 1) as varbinary)
                    AND SUBSTRING(@Name, 2,1) = '''')))

        BEGIN
            SELECT @NameCamelCase = RTRIM(LTRIM(@Name))
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' sr', ' Sr')           
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' jr', ' Jr')       
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' ii', ' II')   
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iii', ' III')
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' DE ', ' de ')
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, 'macdonald', 'MacDonald')

            if (@NameCamelCase LIKE '% iv') -- avoid changing "Ivan" to "IVan"
                SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iv', ' IV')

            if ((@NameCamelCase = 'i') or (@NameCamelCase = 'ii') or (@NameCamelCase = 'iii') or (@NameCamelCase = 'iv'))
                SELECT @NameCamelCase = UPPER(@NameCamelCase)

            RETURN @NameCamelCase       

        END

    ELSE

        BEGIN       

            SELECT @NameCamelCase = RTRIM(LTRIM(@Name))

            -- "Mc-"
            SELECT @NameCamelCase = 
                CASE 
                    WHEN @Name LIKE 'mc%'
                        THEN UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 1)) + UPPER(SUBSTRING(@Name, 3, 1))  + LOWER(SUBSTRING(@Name, 4, 47))
                    ELSE
                       UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 49))
                END

            -- Apostrophes
            SELECT @NameCamelCase = 
                CASE 
                    WHEN @NameCamelCase LIKE '%''%'
                        THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('''', @NameCamelCase) - 1) + ''''  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 2, 50)
                    ELSE
                        @NameCamelCase
                END 


            -- Hyphenated names (do it twice to account for double hyphens)
            SELECT @NameCamelCase = 
                CASE 
                    WHEN @NameCamelCase LIKE '%-%'
                        THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('-', @NameCamelCase) - 1) + '^'  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 2, 50)
                    ELSE
                        @NameCamelCase
                END 

            SELECT @NameCamelCase = 
                CASE 
                    WHEN @NameCamelCase LIKE '%-%'
                        THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('-', @NameCamelCase) - 1) + '^'  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 2, 50)
                    ELSE
                        @NameCamelCase
                END 

            SELECT @NameCamelCase = REPLACE(@NameCamelCase, '^', '-')

            -- Multiple word names (do it twice to account for three word names)
            SELECT @NameCamelCase = 
                CASE 
                    WHEN @NameCamelCase LIKE '% %'
                        THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?'  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50)
                    ELSE
                        @NameCamelCase
                END 

            SELECT @NameCamelCase = 
                CASE 
                    WHEN @NameCamelCase LIKE '% %'
                        THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?'  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50)
                    ELSE
                        @NameCamelCase
                END 

            SELECT @NameCamelCase = REPLACE(@NameCamelCase, '?', ' ')

            -- Names in Parentheses         
            SELECT @NameCamelCase = 
                CASE 
                    WHEN @NameCamelCase LIKE '%(%'
                        THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('(', @NameCamelCase) - 1) + '('  + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 2, 50)
                    ELSE
                        @NameCamelCase
                END 


            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' sr', ' Sr')           
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' jr', ' Jr')           
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' ii', ' II')
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iii', ' III')
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' DE ', ' de ')
            SELECT @NameCamelCase = REPLACE(@NameCamelCase, 'macdonald', 'MacDonald')

            if (@NameCamelCase LIKE '% iv')
                SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iv', ' IV')

            if ((@NameCamelCase = 'i') or (@NameCamelCase = 'ii') or (@NameCamelCase = 'iii') or (@NameCamelCase = 'iv'))
                SELECT @NameCamelCase = UPPER(@NameCamelCase)

            -- Return the result of the function
            RETURN ISNULL(@NameCamelCase, '')

        END

    RETURN ISNULL(@NameCamelCase, '')

END

Upstream

I like where you are going with this. However, this code did not execute for me under SQL Server 2008 R2. The errors were "Must declare the scalar variable "@NameCamelCase".". – Perturbation

Thanks. I was trying to make it a bit simpler and missed a few things. Updating now... – Upstream

voted up for this: "If the name is already in mixed case, trust that it’s right." – Jornada

Answer 6

With SQL 2017 the function could look like this:

create function dbo.cap_words (@str varchar(max))
returns varchar(max)
as
begin
    declare @result varchar(max);
    select @result = string_agg( upper(left(value,1)) + substring(value,2,999),' ') from string_split(lower(@str),' ') 
    return @result;
end

Fredericton

Answer 7

Like me, many people may be looking for an in-query solution, query creating function, well I figured out a different approach:

SELECT REPLACE(
    STUFF( 
        (SELECT' '+ LTRIM(RTRIM(UPPER(SUBSTRING(value, 1,1))+LOWER(SUBSTRING(value, 2, LEN(value)))))
         FROM STRING_SPLIT([Message], ' ')
         FOR XML PATH('')
         ), 1, 1, ''
   ), ''/*Control delimiters here*/, '') FROM [dbo].[MessageQueue]

Change [MessageQueue] table for your own table, and [Message] for your field.

The function STRING_SPLIT may require to increase your SQL compatibility level to 130.

Use the outer REPLACE function to set any delimiter you want.

Moffett

Answer 8

I've taken @ashish.chotalia's answer, and converted it to a temporary procedure with an output parameter. Might be handy for someone if you are not allowed to create functions in production databases, but you do need this functionality.

IF OBJECT_ID('tempdb..#InitCap') IS NOT NULL 
  DROP PROCEDURE #InitCap
GO
CREATE PROCEDURE #InitCap ( @InputString varchar(4000), @OutputString varchar(4000) OUTPUT) 
AS
BEGIN
  DECLARE @Index          INT;
  DECLARE @Char           CHAR(1);
  DECLARE @PrevChar       CHAR(1);

  SET @OutputString = LOWER(@InputString);
  SET @Index = 1;

  WHILE @Index <= LEN(@InputString)
  BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1);
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END;

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
      SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char));
    END;

    SET @Index = @Index + 1;
  END
END
GO

DECLARE @Name NVARCHAR(4000)= 'my string to convert. test/test something:else';
EXEC #InitCap @Name, @OutputString = @Name OUTPUT;
SELECT @Name;

Impetigo

Answer 9

You can use the string_split and string_agg functions to get the desired output. See this example. However, this approach will also replace multiple spaces with a single space in the string.

declare @String nvarchar(max) = 'This   is my string to convert';

select (select string_agg(Stuff(value,1,1,upper(left(value,1))),' ') from string_split(@String,' '))

print @String

-- Output: "This Is My String To Convert"

Filmer

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Cori

Answer 10

Here is simple thing, don't make it complicated.

Oracle: SELECT initcap(lower('This is MY striNg to conVerT')) FROM dual;

Library

The question is regarding sqlserver not oracle. – Petersburg

You can delete your answer to avoid to get negative score – Syringe