Sql: Capitalize First Letter Only

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 <LEN(@string)+1)
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])

How to capitalize the first letter of a record in SQL

Cast your ntext to nvarchar(max) and do the upper and left operations. Sample below.

SELECT UPPER(LEFT(cast(Comments as nvarchar(max)),1)) +
LOWER(SUBSTRING(cast(Comments as nvarchar(max)),2,
LEN(cast(Comments as nvarchar(max)))))  
FROM dbo.Template_Survey;

Following should work for update.

Update dbo.Template_Survey SET Comments = 
UPPER(LEFT(cast(Comments as nvarchar(max)),1)) +
LOWER(SUBSTRING(cast(Comments as nvarchar(max)),2,
LEN(cast(Comments as nvarchar(max)))));  

How to capitalize the first letter of words in Snowflake?

Use initcap() function, it returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.

If you want only first letter in a string to be capitalized, ignoring word delimiters (i.e. the input expression is treated as a single, continuous word), specify empty string as delimiters parameter

INITCAP( str, '')

How to capitalize 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

How to uppercase first letter (without changing the others) in Snowflake

another option is

UPPER(LEFT(my_string,1))||SUBSTR(my_string,2)

and if we use the same whitespace pattern, would win the code golf by 1:

INSERT(my_string,1,1,UPPER(LEFT(my_string,1)))

SQL capitalize first and last letter of a name

The problem is the concatenation character. However, I want to suggest a slightly different approach:

select (upper(substr(last_name, 1, 1)) ||
        substr(last_name, 2, len(last_name) - 2) ||
        upper(substr(last_name, -1, 1))
       )

In other words, avoid initcap(). The issue is unexpected side-effects. initcap() capitalizes the first letter of every word in the string. So, if the name consisted of multiple words, then each would be capitalized. Under such circumstances, I don't think it is wise to assume that the last name consists of only one name.

SQL - Capitalize first letter of each word

You did not say that this was for SQL Server but here is a solution in case it is.

Here is a working sample. Replace the table variable @T with the query you have.

declare @T table(Insurance varchar(max))

insert into @T values
('BENGT MIKAEL ERIKSSON'),
('XMLCHARTEST<>&''"')

select (
       select upper(T.N.value('.', 'char(1)'))+
                lower(stuff(T.N.value('.', 'varchar(max)'), 1, 1, ''))+' '
       from X.InsXML.nodes('/N') as T(N)
       for xml path(''), type
       ).value('.', 'varchar(max)') as Insurance
from 
  (
  select cast('<N>'+replace(
                    replace(
                    replace(Insurance,
                    '&', '&'),
                    '<', '<'),
                    ' ','</N><N>')+'</N>' as xml) as InsXML
  from @T
  ) as X

Result:

Insurance
----------------------
Bengt Mikael Eriksson 
Xmlchartest<>&'" 

---

Related Topics