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 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(''+replace(
replace(
replace(Insurance,
'&', '&'),
'<', '<'),
' ',' ')+' ' as xml) as InsXML
from @T
) as X
Result:
Insurance
----------------------
Bengt Mikael Eriksson
Xmlchartest<>&'"
Capitalize first letter of each word, in existing table
There's no MySQL function to do that, you have to write your own. In the following link there's an implementation:
http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/
In order to use it, first you need to create the function in the database. You can do this, for example, using MySQL Query Browser (right-click the database name and select Create new Function).
After creating the function, you can update the values in the table with a query like this:
UPDATE users SET name = CAP_FIRST(name);
How to capitalize the first letter of words in Snowflake?
Use initcap() function, it returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.
If you want only first letter in a string to be capitalized, ignoring word delimiters (i.e. the input expression is treated as a single, continuous word), specify empty string as delimiters parameter
INITCAP( str, '')
Related Topics
What Are the Most Common SQL Anti-Patterns
Difference Between Primary Key and Unique Key
Error 1046 No Database Selected, How to Resolve
Concatenate Multiple Result Rows of One Column into One, Group by Another Column
Is Having an 'Or' in an Inner Join Condition a Bad Idea
Passing an Array of Parameters to a Stored Procedure
Escaping Ampersand Character in SQL String
Filter Table Before Applying Left Join
Group by Behavior When No Aggregate Functions Are Present in the Select Clause
How to Use on Delete Cascade in MySQL
How to Generate a Range of Dates in SQL Server
Is There a Group_Concat Function in Ms-Access
Concatenate Values Based on Id
Coalesce Alternative in Access SQL
How to Fetch the Row Count for All Tables in a SQL Server Database
Counting Distinct Over Multiple Columns
Converting Epoch Timestamp to SQL Server(Human Readable Format)