How to capitalize the first letter of each word in a string in SQL Server
From http://www.sql-server-helper.com/functions/initcap.aspx
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
BEGIN
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
END
SET @Index = @Index + 1
END
RETURN @OutputString
END
GO
There is a simpler/smaller one here (but doesn't work if any row doesn't have spaces, "Invalid length parameter passed to the RIGHT function."):
http://www.devx.com/tips/Tip/17608
SQL: capitalize first letter only
Are you asking for renaming column itself or capitalise the data inside column? If its data you've to change, then use this:
UPDATE [yourtable]
SET word=UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word)))
If you just wanted to change it only for displaying and do not need the actual data in table to change:
SELECT UPPER(LEFT(word,1))+LOWER(SUBSTRING(word,2,LEN(word))) FROM [yourtable]
Hope this helps.
EDIT: I realised about the '-' so here is my attempt to solve this problem in a function.
CREATE FUNCTION [dbo].[CapitalizeFirstLetter]
(
--string need to format
@string VARCHAR(200)--increase the variable size depending on your needs.
)
RETURNS VARCHAR(200)
AS
BEGIN
--Declare Variables
DECLARE @Index INT,
@ResultString VARCHAR(200)--result string size should equal to the @string variable size
--Initialize the variables
SET @Index = 1
SET @ResultString = ''
--Run the Loop until END of the string
WHILE (@Index <LEN(@string)+1)
BEGIN
IF (@Index = 1)--first letter of the string
BEGIN
--make the first letter capital
SET @ResultString =
@ResultString + UPPER(SUBSTRING(@string, @Index, 1))
SET @Index = @Index+ 1--increase the index
END
-- IF the previous character is space or '-' or next character is '-'
ELSE IF ((SUBSTRING(@string, @Index-1, 1) =' 'or SUBSTRING(@string, @Index-1, 1) ='-' or SUBSTRING(@string, @Index+1, 1) ='-') and @Index+1 <> LEN(@string))
BEGIN
--make the letter capital
SET
@ResultString = @ResultString + UPPER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--increase the index
END
ELSE-- all others
BEGIN
-- make the letter simple
SET
@ResultString = @ResultString + LOWER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--incerase the index
END
END--END of the loop
IF (@@ERROR
<> 0)-- any error occur return the sEND string
BEGIN
SET
@ResultString = @string
END
-- IF no error found return the new string
RETURN @ResultString
END
So then the code would be:
UPDATE [yourtable]
SET word=dbo.CapitalizeFirstLetter([STRING TO GO HERE])
SQL - Capitalize first letter of each word
You did not say that this was for SQL Server but here is a solution in case it is.
Here is a working sample. Replace the table variable @T
with the query you have.
declare @T table(Insurance varchar(max))
insert into @T values
('BENGT MIKAEL ERIKSSON'),
('XMLCHARTEST<>&''"')
select (
select upper(T.N.value('.', 'char(1)'))+
lower(stuff(T.N.value('.', 'varchar(max)'), 1, 1, ''))+' '
from X.InsXML.nodes('/N') as T(N)
for xml path(''), type
).value('.', 'varchar(max)') as Insurance
from
(
select cast('<N>'+replace(
replace(
replace(Insurance,
'&', '&'),
'<', '<'),
' ','</N><N>')+'</N>' as xml) as InsXML
from @T
) as X
Result:
Insurance
----------------------
Bengt Mikael Eriksson
Xmlchartest<>&'"
Capitalise first letter of each word without changing currently Capitalised letters
It appears that the following line is responsible for lowercasing the entire input string:
SET @OutputString = LOWER(@InputString)
Then, the function selectively upper cases what it perceives to be the start of each word. Replace the above line with the following assignment and the original case of the input string should be preserved:
SET @OutputString = @InputString
How to capitalize the first letter of a record in SQL
Cast your ntext to nvarchar(max) and do the upper and left operations. Sample below.
SELECT UPPER(LEFT(cast(Comments as nvarchar(max)),1)) +
LOWER(SUBSTRING(cast(Comments as nvarchar(max)),2,
LEN(cast(Comments as nvarchar(max)))))
FROM dbo.Template_Survey;
Following should work for update.
Update dbo.Template_Survey SET Comments =
UPPER(LEFT(cast(Comments as nvarchar(max)),1)) +
LOWER(SUBSTRING(cast(Comments as nvarchar(max)),2,
LEN(cast(Comments as nvarchar(max)))));
Related Topics
How to Find Multiple Occurrence of Particular String and Fetch Value in SQL Server
How to Get the Month and Day With Leading 0'S in SQL (E.G. 9 => 09)
Sql Select Join: How to Prefix All Columns as 'Prefix.*'
Delete Rows With Date Older Than 30 Days With SQL Server Query
How to Change Column Order in a Table Using SQL Query in SQL Server 2005
How to Get Last 7 Days Data from Current Datetime to Last 7 Days in SQL Server
How to Add a Space Between Two Text in SQL Code
Checking If a SQL Server Login Already Exists
Select Distinct Values from One Table and Join With Another Table
Sql Server How to Return Null Instead of 0 If a Grouped Value Doesn't Have Rows in Source
How to Execute a Stored Procedure Once for Each Row Returned by Query
Sql Server: Create an Incremental Counter for Records in the Same Year
Sql - Select Parent and Child Records in an Order
How to Calculate Age (In Years) Based on Date of Birth and Getdate()
How to Retrieve Microseconds or Milliseconds from MySQL Current Time
How to Have a Default Parameter for a MySQL Stored Procedure
How to Select Rows With Only Numeric Characters in Oracle SQL