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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case – Part 2

  1. 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

  2. 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.

    • 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 ) = ‘,’

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s