Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server # TSQL to Convert STRING in PROPER format

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.

Comments

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.

Leave a Comment

Please register or log in to leave a comment.