What’s the best way to capitalise the first letter of each word in a string in SQL Server

what'd
what w
what does
how to pronounce what
what's
whats the meaning of
what is love
what c

What’s the best way to capitalise the first letter of each word in a string in SQL Server.


From http://www.sql-server-helper.com/functions/initcap.aspx

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(255)

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
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END
GO

There is a simpler/smaller one here (but doesn't work if any row doesn't have spaces, "Invalid length parameter passed to the RIGHT function."):

http://www.devx.com/tips/Tip/17608

What, what definition: 1. used to ask for information about people or things: 2. used in questions that show you are…. Learn more. Quickly send and receive WhatsApp messages right from your computer.


If you are looking for the answer to the same question in Oracle/PLSQL then you may use the function INITCAP. Below is an example for the attribute dname from a table department which has the values ('sales', 'management', 'production', 'development').

SQL> select INITCAP(dname) from department;

INITCAP(DNAME)
--------------------------------------------------
Sales
Management
Production
Development

Drake - Nice For What, “What might the impact be of telling them that they can't cry at work?” But instead of taking a coaching approach, I responded instinctively and  noun the true nature or identity of something, or the sum of its characteristics: a lecture on the whats and hows of crop rotation.


A variation of the one I've been using for quite some time is:

CREATE FUNCTION [widget].[properCase](@string varchar(8000)) RETURNS varchar(8000) AS
BEGIN   
    SET @string = LOWER(@string)
    DECLARE @i INT
    SET @i = ASCII('a')
    WHILE @i <= ASCII('z')
    BEGIN
        SET @string = REPLACE( @string, ' ' + CHAR(@i), ' ' + CHAR(@i-32))
        SET @i = @i + 1
    END
    SET @string = CHAR(ASCII(LEFT(@string, 1))-32) + RIGHT(@string, LEN(@string)-1)
    RETURN @string
END

You can easily modify to handle characters after items other than spaces if you wanted to.

what, Usage Note: When what is the subject of a clause, it takes a singular verb if the word or phrase that completes the sentence (the complement) is singular, as in I​  what definition: 1. used to ask for information about people or things: 2. used in questions that show you are…. Learn more.


Another solution without using the loop - pure set-based approach with recursive CTE

create function [dbo].InitCap (@value varchar(max))
returns varchar(max) as
begin

    declare
        @separator char(1) = ' ',
        @result varchar(max) = '';

    with r as (
        select value, cast(null as varchar(max)) [x], cast('' as varchar(max)) [char], 0 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
        union all
        select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
        , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
        , left(r.[value], 1)
        , [no] + 1 [no]
        from r where value > '')

    select @result = @result +
    case
        when ascii([char]) between 97 and 122 
            then stuff(x, 1, 1, char(ascii([char])-32))
        else x
    end + @separator
    from r where x is not null;

    set @result = rtrim(@result);

    return @result;
end

WHAT, what definition: The definition of what is which one or as many as. (adjective) An example of what used as an adjective is in the sentence, "What shirt do you  whatever: adverb at all , of any description , of any kind or sort , whatsoever , whichever


Here is the simplest one-line code.

select 
        LEFT(column, 1)+ lower(RIGHT(column, len(column)-1) )
     from [tablename]

What to Say When Someone Cries at Work, On Thursday, the eye-mouth-eye emoji meme started appearing in people's Twitter handles and almost immediately, the buzz started about what  what definition: The definition of what is which one or as many as. (adjective) An example of what used as an adjective is in the sentence, "What shirt do you want?"


What, What Do You Do When Extremism Comes for the Hawaiian Shirt? Worn by extremists toting assault rifles, the shirt has gone from dad symbol to  WhatsApp Messenger: More than 2 billion people in over 180 countries use WhatsApp to stay in touch with friends and family, anytime and anywhere. WhatsApp is free and offers simple, secure, reliable messaging and calling, available on phones all over the world.


What dictionary definition, Translate What. See 8 authoritative translations of What in Spanish with example sentences and audio pronunciations.


It Is What It Is: a fake app hyped on Twitter turned into a fundraiser , What Is My IP? WhatIsMyIP.com® is the industry leader in providing REAL IP address information. We provide IP address tools that allow users to perform an Internet Speed Test, IP address lookup, proxy detection, IP Whois Lookup, and more.