?
Solved

SQL FUNCTION TO Capitalize Each Word in a String

Posted on 2016-10-21
10
Medium Priority
?
135 Views
Last Modified: 2016-11-04
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
Comment
Question by:josephdts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 13

Expert Comment

by:Dustin Saunders
ID: 41854478
in SQL syntax UPPER will set a string to uppercase.  This works with accented letters.

SELECT UPPER('éastman')

Open in new window

0
 
LVL 23

Assisted Solution

by:Snarf0001
Snarf0001 earned 600 total points
ID: 41854690
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1400 total points
ID: 41854837
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41855718
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41855723
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41855731
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
 

Author Comment

by:josephdts
ID: 41874654
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
 

Author Comment

by:josephdts
ID: 41874656
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
 

Author Comment

by:josephdts
ID: 41874659
no more comments, just a confusing web site
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41874797
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question