Link to home
Start Free TrialLog in
Avatar of josephdts
josephdts

asked on

SQL FUNCTION TO Capitalize Each Word in a String

The following function capitalizes each word in a string. How do I make it accept accented characters?
E.G
select dbo.InitialCap('MY TEST')  gives result 'My Test'
How do I make it accept accented characters like 'É'? For now,  select dbo.InitialCap('MY TÉST') returns My TÉSt
The code is below:
ALTER FUNCTION [dbo].[InitialCap](@String VARCHAR(8000))
                  RETURNS VARCHAR(8000)
                 AS
 BEGIN

                   DECLARE @Position INT;

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

                    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 @String;
  END ;
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

in SQL syntax UPPER will set a string to uppercase.  This works with accented letters.

SELECT UPPER('éastman')

Open in new window

SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Solution 1 - Pawan - Version 1

DECLARE @InputString VARCHAR(1000) =  '7What the 789hell is 78 going on here dshf 78'
DECLARE @OutputString VARCHAR(1000) = ''
 
SELECT
        @OutputString = @OutputString +
        CASE
            WHEN Number = 1 THEN UPPER(SUBSTRING(@InputString,Number,1))
            WHEN SUBSTRING(@InputString,Number-1,1) = '' THEN UPPER(SUBSTRING(@InputString,Number,1))
        ELSE
            LOWER(SUBSTRING(@InputString,Number,1))
        END
FROM
(
    SELECT DISTINCT Number
    FROM MASTER..SPT_VALUES N
    WHERE Number > 0 AND Number < LEN(@InputString)
)u
 
SELECT @OutputString Outputs
 
GO

Open in new window



SOLUTION 2 - George Mastros | Modified Version

DECLARE @String VARCHAR(1000) =  '7what the 789hell is 78 going on here dshf 78'
DECLARE @Position INT = -1
 
SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1)))
SELECT @Position = PATINDEX('%[^A-Za-z0-9''][a-z]%',@String COLLATE Latin1_General_Bin)
 
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))),
@Position = PATINDEX('%[^A-Za-z0-9''][a-z]%',@String COLLATE Latin1_General_Bin)
 
SELECT @String

Open in new window


If you want to understand this in detail refer my blog - https://msbiskills.com/2016/06/26/sql-server-puzzle-make-all-upperlower-case-of-string-to-proper-casetitle-case-multiple-methods/

Enjoy !!
Pawan, the first option cuts the last character from the string fix that (in line 16) by:

    WHERE Number > 0 AND Number <= LEN(@InputString)
or
    WHERE Number > 0 AND Number < LEN(@InputString) + 1
Ohh Yes Thanks Paul !!

Updated..

DECLARE @InputString VARCHAR(1000) =  '7What the 789hell is 78 going on here dshf 78'
DECLARE @OutputString VARCHAR(1000) = ''
 
SELECT
        @OutputString = @OutputString +
        CASE
            WHEN Number = 1 THEN UPPER(SUBSTRING(@InputString,Number,1))
            WHEN SUBSTRING(@InputString,Number-1,1) = '' THEN UPPER(SUBSTRING(@InputString,Number,1))
        ELSE
            LOWER(SUBSTRING(@InputString,Number,1))
        END
FROM
(
    SELECT DISTINCT Number
    FROM MASTER..SPT_VALUES N
    WHERE Number > 0 AND Number <= LEN(@InputString)
)u
 
SELECT @OutputString Outputs
 
GO

Open in new window

Avatar of josephdts
josephdts

ASKER

The best solution has allowed me more flexibility. I have concentrated on these two solutions, because the other two did not test for the Characters not found in English, but found in other Latin languages.
The best solution has allowed me more flexibility. I have concentrated on these two solutions, because the other two did not test for the Characters not found in English, but found in other Latin languages.
no more comments, just a confusing web site
If you have chosen the best solution then normally the next step is to click a button to choose that best solution and close the question.

As I write this it is 7:00am. Sometimes there are delays in response because of time zones.

If you have a problem with the solutions suggested here please ask.