Subscribe to this blog
Briefcase
Print
Problem Statement
SQL Server has got in-built functions to convert the given string into LOWER() or UPPER() format but it does not provides any direct way to convert it to PROPER format. A string/text is said to be in a PROPER format if all the words in that string/text starts with a CAPITAL letter.
E.g. If a string is - “Hello, how are you?”,
String in Lower format = “hello, how are you?”
String in Upper format = “HELLO, HOW ARE YOU?”
and String in Proper format = “Hello, How Are You?”
Implementation
Ideally, SQL Server is not the right place to implement this kind of logic, as string operations are costlier in SQL from performance perspective. it should be either implemented in the Front-End language or the Reporting Tool as this more related to the formatting. However, if this is to be implemented in SQL, the more preferred way is to use SQL-CLR function. It does not mean that we can not achieve this with T-SQL.
Today, I will share a simple T-SQL function, which could be used to convert any given string in PROPER format. Below is the script -
CREATE FUNCTION [dbo].[PROPER]
(
@StrToConvertToProper AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
--Trim the Text
SET @StrToConvertToProper = LTRIM(RTRIM(@StrToConvertToProper))
--Find the No. of Words in the Text
DECLARE @WordCount AS INT
SET @WordCount = LEN(@StrToConvertToProper) - LEN(REPLACE(@StrToConvertToProper,' ','')) + 1
--Variable to track the space position
DECLARE @LastSpacePosition AS INT = 0
--Loop through all the words
WHILE(@WordCount > 0)
BEGIN
--Set the Space Position
SET @LastSpacePosition = CHARINDEX(' ',@StrToConvertToProper,@LastSpacePosition + 1)
--Replace the Character
SET @StrToConvertToProper = STUFF(@StrToConvertToProper,
@LastSpacePosition + 1,
1,
UPPER(SUBSTRING(@StrToConvertToProper, @LastSpacePosition + 1, 1)))
--Decrement the Loop counter
SET @WordCount = @WordCount - 1
END
RETURN @StrToConvertToProper
END
When the above script is used as –
SELECT dbo.PROPER('hello, how are you?')
we get the following result - Hello, How Are You?
Conclusion
The given script could be used to convert any string in PROPER format using T-SQL. However, I would personally prefer converting the string at the Front-End or in the Reporting tool to display the string in this format.
Posted by angelushka on 18 October 2012
There is a small issue.
If you have a string like 'hello,how are you?'
The result will be 'Hello,how Are You?'
here's another one, works for all punctation:
create function ProperCase(@Text as varchar(MAX))
returns varchar(MAX)
as
begin
declare @Reset bit;
declare @Ret varchar(MAX);
declare @i int;
declare @c char(1);
select @Reset = 1, @i=1, @Ret = '';
while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1
return @Ret
end
Posted by angelushka on 18 October 2012
There is a small issue.
If you have a string like 'hello,how are you?'
The result will be 'Hello,how Are You?'
here's another one, works for all punctation:
create function ProperCase(@Text as varchar(MAX))
returns varchar(MAX)
as
begin
declare @Reset bit;
declare @Ret varchar(MAX);
declare @i int;
declare @c char(1);
select @Reset = 1, @i=1, @Ret = '';
while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1
return @Ret
end
Posted by seankyleprice on 18 October 2012
This can be done using recursive CTEs which is generally a bit quicker.
DECLARE @str as varchar(max) = 'heLlo, hoW are you?'
;With tmp as
(
select
1 as cnt,
SUBSTRING(@str, 1, 1) ltr,
cast('' as varchar(max)) txt,
cast(' ' as varchar(max)) pre
union all
select
cnt + 1,
SUBSTRING(@str, cnt, 1),
case when pre = ' ' then txt + UPPER(SUBSTRING(@str, cnt, 1)) else txt + LOWER(SUBSTRING(@str, cnt, 1)) end,
SUBSTRING(@str, cnt, 1)
from tmp
where cnt <= LEN(@str)
)
select txt from tmp where cnt = LEN(@str) + 1
Posted by crussell-931424 on 18 October 2012
I like the simplicity. It doesn't try to do anything more than just cap the first letter. If there are extra spaces it leaves them alone. If short a space it doesn't try to insert one. It has one purpose and does it well.
The problem with the "smarter" code (see other comments) is that it ends up messing things up. For example the upper lower ends up changing a doctor's credentials to Md rather than leaving it alone as MD.
Posted by RLilj33 on 18 October 2012
I agree with crussel-931424 - simple and clean.
I've been doing a lot of code optimization lately using pseudo cursors, so thought I'd take a break & have some fun with it.
This code does the same conversion using a tally table instead of a loop. I provide it here as an alternate look at SQL - like seankyleprice did with a recursive CTE.
CREATE FUNCTION [dbo].[PROPER2]
(
@StrToConvertToProper AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @chvNewString VARCHAR(MAX);
-- Prep the return string with the first character converted to uppercase
SET @chvNewString = UPPER(LEFT(@StrToConvertToProper, 1));
SELECT @chvNewString = @chvNewString +
CASE
-- Looks for the combination of a space + non-space ('_x')
WHEN SUBSTRING(@StrToConvertToProper, T.tally_id-1, 1) = ' '
AND SUBSTRING(@StrToConvertToProper, T.tally_id, 1) <> ' '
-- When found, appends the non-space character converted to uppercase
-- Note: the UPPER function will correctly handle non-alpha characters
THEN UPPER(SUBSTRING(@StrToConvertToProper, T.tally_id, 1))
-- If not found, appends the character
ELSE SUBSTRING(@StrToConvertToProper, T.tally_id, 1)
END
FROM tally T
WHERE T.tally_id BETWEEN 2 AND LEN(@StrToConvertToProper);
RETURN @chvNewString;
END
Posted by vinaypugalia on 18 October 2012
Thanks to all for sharing your code.
Actually, I believe, no code is correct or in-correct, what is correct is only what suits ones situation.
However, it is really encouraging to see so many flavors of achieving any given goal.
I really appreciate and request if some more readers could share their flavors too.
Posted by Roelof-250785 on 18 October 2012
Is there a good reason for using varchar instead of nvarchar? I suppose some of the unicode characters may not have the concept of upper case characters applied... but what if they do?
Posted by Roelof-250785 on 18 October 2012
Is there a good reason for using varchar instead of nvarchar? I suppose some of the unicode characters may not have the concept of upper case characters applied... but what if they do?
Posted by vinaypugalia on 18 October 2012
No, there is no specific reason of using VARCHAR instead of NVARCHAR.
However, I believe it should make no difference if we change the implementation to NVARCHAR as UPPER() will take care of it.