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
I want a first letter of each word is in capital so tables look like below after updation .
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.
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.
SELECT Id,dbo.firstCharUpperCase(title),IsActive FROM CodeTable
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 .
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
Id | Title | IsActive |
---|---|---|
1 | Why sql server is intresting. | 1 |
2 | How to update first letter of word is always capital in SQL Server | 1 |
I want a first letter of each word is in capital so tables look like below after updation .
Id | Title | IsActive |
---|---|---|
1 | Why Sql Server Is Intresting. | 1 |
2 | How To Update First Letter Of Word Is Always Capital In SQL Server | 1 |
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
(
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.Id | Title | IsActive |
---|---|---|
1 | Why Sql Server Is Intresting. | 1 |
2 | How To Update First Letter Of Word Is Always Capital In Sql Server | 1 |
3 | New Topic Added In Code | 1 |
Explanation:
Note: Code Tested in SQL SERVER 2008R2.
why it is not SELECT3 in insertion block
ReplyDeleteSELECT 2,'New topic added in code',1
Its by mistake .i correct it . Thanks for pointing this .
Delete