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

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 o开发者_JS百科utput: This Is My String To Convert

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 

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'

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

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:

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

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

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.

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

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