first letter capital of the word

Question

Post reply

first letter capital of the word

Shree23

SSC-Addicted

Points: 497
More actions
March 24, 2022 at 3:30 pm
Go to Answer
#4005400

Hi Expert,

how to make first letter capital of the word
create table main13( First_lastname Nvarchar (100))
insert main13 values('18 - EW MEMBER; OLD MEEMBER'),
('18 - VOYGER'),
('1 - ONE DAY')
EXPECTED Output

Jonathan AC Roberts

SSCoach

Points: 18355

March 24, 2022 at 5:14 pm

Answer

#4005494

Create this function

CREATE FUNCTION dbo.TitleCase
(
    @String nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
RETURN stuff(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
       replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
       lower(@String),' A',' A'),' B',' B'),' C',' C'),' D',' D'),' E',' E'),' F',' F'),' G',' G'),' H',' H'),
       ' I',' I'),' J',' J'),' K',' K'),' L',' L'),' M',' M'),' N',' N')
    ,' O',' O'),' P',' P'),' Q',' Q'),' R',' R'),' S',' S'),' T',' T'),' U',' U'),' V',' V'),' W',' W'),' X',' X'),' Y',' Y'),' Z',' Z'),1, 1, UPPER(LEFT(@String,1)))

END
GO

Then call it like this:

SELECT dbo.TitleCase(First_lastname)
FROM main13;

titleCase

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

There is nothing built in to T-SQL which will do this for you. If you search for "T-SQL Proper Case Function" you will find loads of examples that others have written. Most seem to use a WHILE loop, so they're not going to run very quickly. A custom CLR function might be the fastest way, if you are using this on lots of rows.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 2

Honestly, I'd suggest doing this in the presentation layer. In, for example, you are in SSRS you could use an expression like this:

=StrConv(Fields!YourColumn.Value, vbProperCase)

C# has the string function ToTitleCase, and I'm sure what ever language you are using has something equivilent.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Shree23 SSC-Addicted Points: 497 More actions · Answer 3

what a solution ..is there any easiest way to do this just want to have capital word after space

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 4

Shree23 wrote:

what a solution ..is there any easiest way to do this just want to have capital word after space

I've done the difficult bit for you (writing the function) I can't see how it can get much easier than copying and pasting a function into SSMS and running it, then just calling the function from your SQL statement.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

After specifically a space, as you stated, that method's likely the best way. But if you need to consider chars other than a space before the letter, then you'll need a different method of course.

For example, like any of the values below:

('22 - MEMBER1 (HEAD OF GROUP)'),

('24 - MEMBER_A/MEMBER_B'),

('26 - ABC-DEF')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".