CODEWITHSUNDEEP
t-sql
HerrimanCoder
HerrimanCoder

Reputation: 7218

T-SQL initial capitalization - leave 2-n chars alone

I have a t-sql function I found online, maybe on stack, that works perfectly for camel-casing all words in a string:

CREATE FUNCTION InitialCap(@String VARCHAR(75))
    RETURNS VARCHAR(75)
AS
BEGIN 

DECLARE @Position INT;

SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) , @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String);

WHILE @Position > 0
    SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin);
    RETURN ISNULL(@String,'');

END

The problem is that I now need a slight mod of this, such that it leaves alone all but the first letter of each word. I still want it to capitalize the first letter of each word, but then I don't want it to ever change other letters in that word. So here are some examples of my desired outcome:

'here is foo' -> 'Here Is Foo'
'i like the FBI' -> 'I Like The FBI'

For the 2nd example, note that my function shown above changes "FBI" to "Fbi", and that's the problem. I'm not sure how to change the sql to achieve it so that it would leave FBI as FBI. Now of course if the original string were "fbi", I know it would change it to "Fbi", and that's fine.

In summary, I never want the function to change an upper case letter to lower case.

Upvotes: 1

Views: 71

Answers (1)

Stephan
Stephan

Reputation: 6018

Just get rid of LOWER() in STUFF():

CREATE FUNCTION InitialCap(@String VARCHAR(75))
    RETURNS VARCHAR(75)
AS
BEGIN 

DECLARE @Position INT;

SELECT @String = STUFF(@String,1,1,UPPER(LEFT(@String,1))) , @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String);

WHILE @Position > 0
    SELECT @String   = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin);
    RETURN ISNULL(@String,'');

END
GO

DECLARE @String VARCHAR(75) = 'i like the FBI'
SELECT dbo.InitialCap(@String)

Results:

I Like The FBI

Upvotes: 2

Related Questions