• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

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 ;
0
josephdts
Asked:
josephdts
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
Dustin SaundersDirector of OperationsCommented:
in SQL syntax UPPER will set a string to uppercase.  This works with accented letters.

SELECT UPPER('éastman')

Open in new window

0
 
Snarf0001Commented:
Upper is already being used, it's the PatIndex that's causing the issue, because you're specifically looking for non-accented characters.

If you change to charindex and scan for the spaces, it should would properly with slight modification:

alter function dbo.InitialCap (@value varchar(max))
	returns varchar(max)
as
begin

	declare @pos int = 1
	set @value = lower(@value)

	while (@pos > 0)
	begin
		select @value = stuff(@value, @pos, 1, upper(substring(@value, @pos, 1))), @pos = charindex(' ', @value, @pos)
		if(@pos > 0) set @pos = @pos + 1
	end

	return @value

end

Open in new window

0
 
PortletPaulCommented:
before: la volpe marrone veloce òsalt sul cane pigro
after:    La Volpe Marrone Veloce Òsalt Sul Cane Pigro

(ps: it's nonsense I just wanted an accented lower case character, it should read
La Volpe Marrone Veloce Saltò Sul Cane Pigro)

The function that follows tests for the "previous character" and if that is in these:
        (   ' '  , ';',   ':',   '!',   '?',   ',',   '.',   '_',   '-',   '/',   '&',   '''',   '('    )
the following character is made upper case.

The following is a derivative, just adding NCHAR & NVARCHAR
CREATE FUNCTION [dbo].[InitCap] ( @InputString nvarchar(4000) ) 
RETURNS NVARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           NCHAR(1)
DECLARE @PrevChar       NCHAR(1)
DECLARE @OutputString   NVARCHAR(4000)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END
GO

Open in new window

Sourced from http://www.sql-server-helper.com/functions/initcap.aspx
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Pawan KumarDatabase ExpertCommented:
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 !!
0
 
PortletPaulCommented:
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
1
 
Pawan KumarDatabase ExpertCommented:
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

0
 
josephdtsAuthor Commented:
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.
0
 
josephdtsAuthor Commented:
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.
0
 
josephdtsAuthor Commented:
no more comments, just a confusing web site
0
 
PortletPaulCommented:
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now