CODEWITHSUNDEEP
sqlsql-server-2008
MG SQL Muppet
MG SQL Muppet

Reputation: 127

How to capitalize the first letter of a record in SQL

I have a table with a Comments field and I want to capitalize the first letter of the string in each record eg change 'duplicate' into 'Duplicate'. Some records will already have a capital at the start, and some may even be a number.

I tried this

SELECT UPPER(LEFT(Comments,1))+SUBSTRING(Comments,2,LEN(Comments)) FROM dbo.Template_Survey

but it fails because my field is an 'ntext' type.

It would also be useful to be able to capitalize the first letter after a full stop (period) but this is not essential unless someone has the code already written.

Thanks in advance.

Upvotes: 2

Views: 30073

Answers (4)

ButcherFromHell
ButcherFromHell

Reputation: 165

or:

UPDATE `table` SET name = CONCAT(UPPER(LEFT(name,1)),LOWER(RIGHT(name,LENGTH(name)-1)))

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

A bit late on my post here, but this solution is shorter. This works in sql server 2008+:

SELECT
  UPPER(CAST(Comments as nchar(1)))+LOWER(STUFF(CAST(Comments as nvarchar(max)),1,1,''))
FROM dbo.Template_Survey

Upvotes: 0

Reza
Reza

Reputation: 19863

Cast you column to nvarchar(max)

here is the working example

http://sqlfiddle.com/#!3/5dd26/3

To update records you can use:

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

Upvotes: 2

user353gre3
user353gre3

Reputation: 2755

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

Upvotes: 4

Related Questions