Query to update first letter of word is in capital in SQL Server.

By

Update First letter In SQL Server
Here i learn good query with select command . I have a case in SQL server suppose i have a sentence and i have to convert first letter of each word in this sentence to Capital letter.To do this i have to use off course SQL Sever select command  but we have to implement some logic here also.
I am using here ASCII  In SQL Server. I discuss ASCII in SQL Server Letter.
 i have a column name is title varchar(500) and it have data .
Tables look like below

IdTitleIsActive
1Why sql server is intresting.1
2How to update first letter of word is always capital in SQL Server1

 I want a first letter of each word is in capital so tables look like below after updation .

IdTitleIsActive
1Why Sql Server Is Intresting.1
2How To Update First Letter Of Word Is Always Capital In SQL Server1

Some time its client requirement to show data in such a manner. First i create a table and insert data as you given and after that write a query for this.

Solution is

CREATE TABLE CodeTitleTable
(
Id int ,
Title varchar(500),
IsActive bit
)

Insert some data in above table

INSERT INTO CodeTable
SELECT 1,'Why Sql Server Is Intresting',1
UNION
SELECT 2,'How to update first letter of word is always capital in SQL Server',1
UNION
SELECT 3,'New topic added in code',1

After insert data then data look like above . Now I write down a function  for above requirement and after that call this function to query.

CREATE FUNCTION firstCharUpperCase(@input varchar(8000)) RETURNS varchar(8000) AS
BEGIN 
    SET @input = LOWER(@input)
    DECLARE @id INT
    SET @id = ASCII('a')
    WHILE @id <= ASCII('z')
    BEGIN
        SET @input= REPLACE(@input, ' ' + CHAR(@id), ' ' + CHAR(@id-32))
        SET @id= @id+ 1
    END
    SET @input = CHAR(ASCII(LEFT(@input, 1))-32) + RIGHT(@input, LEN(@input)-1)
    RETURN @input

END

SELECT Id,dbo.firstCharUpperCase(title),IsActive FROM CodeTable

Output of above query is:

When you run above query then output is exactly same as you want.
IdTitleIsActive
1Why Sql Server Is Intresting.1
2How To Update First Letter Of Word Is Always Capital In Sql Server1
3New Topic Added In Code1

Explanation:

Here I use only ASCII ,Replace and While loop.I use @id-32 because we know the ASCII value of 'a' is 97 and  ASCII value of 'A' is  65. So 97-32=65 .Replace is used for replace the small letter to capital letter. Hope this will help you .

Note: Code Tested in SQL SERVER 2008R2. 

2 comments:

  1. why it is not SELECT3 in insertion block

    SELECT 2,'New topic added in code',1

    ReplyDelete
    Replies
    1. Its by mistake .i correct it . Thanks for pointing this .

      Delete