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
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Position INT;
SELECT @String = STUFF(LOWER(@String),1,1,U
@Position = PATINDEX('%[^A-Za-z''][a-z
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,
@Position = PATINDEX('%[^A-Za-z''][a-z
RETURN @String;
END ;
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
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Position INT;
SELECT @String = STUFF(LOWER(@String),1,1,U
@Position = PATINDEX('%[^A-Za-z''][a-z
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,
@Position = PATINDEX('%[^A-Za-z''][a-z
RETURN @String;
END ;
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
PortletPaul
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Solution 1 - Pawan - Version 1
SOLUTION 2 - George Mastros | Modified Version
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 !!
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
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
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
WHERE Number > 0 AND Number <= LEN(@InputString)
or
WHERE Number > 0 AND Number < LEN(@InputString) + 1
Ohh Yes Thanks Paul !!
Updated..
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
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.
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.
ASKER
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.
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.
Open in new window