This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.
Course Of The Month
5 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL FUNCTION TO Capitalize Each Word in a String
Posted on 2016-10-21
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 ;
[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
Active 1 day ago
in SQL syntax UPPER will set a string to uppercase. This works with accented letters.
SELECT UPPER('éastman')
Active 2 days ago
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:
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
Active today
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
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
Active today
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 !!
Active today
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
Active today
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
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.
Active today
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.
Featured Post
Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month5 days, 16 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.