SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case – Part 2

I had previously written SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case and I had really enjoyed writing it. Above script converts first letter of each word from sentence to upper case.

For example
this function will convert this string to title case!
will be converted to
This Function Will Convert This String To Title Case!
However if you just want to convert first word of complete sentence you can use following quick script.

USE AdventureWorks
GO
DECLARE @varString VARCHAR(100)
SET @varString = 'this function will convert this string to title case!'
SELECT UPPER(SUBSTRING(@varString,1,1))+SUBSTRING(@varString,2,LEN(@varString))
GO

For example
this function will convert this string to title case!
will be converted to
This function will convert this string to title case!
If you have column of table which needs to be converted to “sentenced case” – where only first char of sentence is upper cased.

Following script written by Imran will show how to convert the first char of sentence or word group to be upper case in any table.

USE AdventureWorks
GO
CREATE TABLE example (ename VARCHAR(100))
GO
INSERT INTO example
VALUES ('good morning')
INSERT INTO example
VALUES ('hello')
GO
SELECT UPPER(SUBSTRING(ename,1,1))+SUBSTRING(ename,2,LEN(ename))
FROM example
GO
DROP TABLE example
GO

SQL SERVER - UDF - Function to Convert Text String to Title Case - Proper Case - Part 2 sentencedcase

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Behind the Scene of SQL Server Activity of – Transaction Log – Shrinking Log
Next Post
SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

Related Posts

7 Comments. Leave new

  • Sir,

    This is not regarding for this post.but i want to ask a question.

    what happened when i create a column with int datatype and autoincrement. and finally limit of int datatype has been finished now insertion of next row there will be error or something ?

    Thanks
    Sachin Kulshreshtha

    Reply
  • Hai,
    If I am not wrong, your codes do not change the value, for you only choose “SELECT” key. How if do I want to change the data permanently (Updated)?

    For example, I write these codes to change all strings in a column to upper case :
    “update Table_A set Field_A=UPPER(Field_A)….”

    My question is how to change the string to Proper or title one.

    Thanks.

    Reply
  • hi,
    If there any way to spilt the sentence using any delimeter and insert into table.

    Regards
    Vinoth N

    Reply
    • DECLARE @string VARCHAR(8000)

      SELECT @string = ‘this,is,test’

      SELECT
      string = SUBSTRING(@string, n, CHARINDEX(‘,’, @string + ‘,’, n ) – n)
      FROM
      (
      SELECT number as n FROM master..spt_values
      where type=’p’
      ) as n
      WHERE
      SUBSTRING( ‘,’ + @string, n, 1 ) = ‘,’

      Reply
  • Thanigaimani Thirumalai
    October 26, 2012 5:33 pm

    Thanks Pinal Dave

    Reply
  • srinivasrayabandi
    January 25, 2013 11:17 am

    Thanks Pinal Dave

    Reply
  • srinivasrayabandi
    January 25, 2013 11:18 am

    Thanks

    Reply

Leave a Reply

Menu