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

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

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

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...