Answered by:
Similar to Initcap in SQL server T-SQL

Question
-
Hi Experts,
I would like to convert first letter of the word as Upper and remaining in Lower.
select [Name] from Table
now I had names like this JOHN BRAIN
LIONA IAN O'CONNOR
So I wold like to see them as John Brain
Liona Ian O'Connor
How to do that could anyone guide me please
Thanks,
RK
Monday, December 10, 2012 2:01 PM
Answers
-
It is a bit tricky and you may need to use a function. Take a look at this one
SQL Server Proper Case Function
We made some slight modifications in that function to cover complex cases like you list
create function [dbo].[Proper] (@cStringToProper varchar(8000)) returns varchar(8000) as begin declare @Position int --** Adding next line to handle O', Mc and Mac names select @cStringToProper = replace(replace(replace(replace(@cStringToProper, '-', '} '), '''', '| '), ' MC', '{ '), ' MAC', '* ') select @cStringToProper = stuff(lower(@cStringToProper) , 1 , 1 , upper(left(@cStringToProper , 1))) , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin) while @Position > 0 select @cStringToProper = stuff(@cStringToProper , @Position , 2 , upper(substring(@cStringToProper , @Position , 2))) , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin) --** -- Adding next line to handle O', Mc and Mac names select @cStringToProper = replace(replace(replace(replace(@cStringToProper, '* ', ' Mac'), '{ ', ' Mc'), '| ', ''''), '} ', '-') return @cStringToProper end
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Proposed as answer by Pieter Geerkens Monday, December 10, 2012 2:51 PM
- Marked as answer by BandSr Monday, December 10, 2012 4:38 PM
Monday, December 10, 2012 2:05 PM
All replies
-
It is a bit tricky and you may need to use a function. Take a look at this one
SQL Server Proper Case Function
We made some slight modifications in that function to cover complex cases like you list
create function [dbo].[Proper] (@cStringToProper varchar(8000)) returns varchar(8000) as begin declare @Position int --** Adding next line to handle O', Mc and Mac names select @cStringToProper = replace(replace(replace(replace(@cStringToProper, '-', '} '), '''', '| '), ' MC', '{ '), ' MAC', '* ') select @cStringToProper = stuff(lower(@cStringToProper) , 1 , 1 , upper(left(@cStringToProper , 1))) , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin) while @Position > 0 select @cStringToProper = stuff(@cStringToProper , @Position , 2 , upper(substring(@cStringToProper , @Position , 2))) , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin) --** -- Adding next line to handle O', Mc and Mac names select @cStringToProper = replace(replace(replace(replace(@cStringToProper, '* ', ' Mac'), '{ ', ' Mc'), '| ', ''''), '} ', '-') return @cStringToProper end
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Proposed as answer by Pieter Geerkens Monday, December 10, 2012 2:51 PM
- Marked as answer by BandSr Monday, December 10, 2012 4:38 PM
Monday, December 10, 2012 2:05 PM -
Thanks for your reply. I don't want to create the function. is that possible without using function...
I want to implement that in the view.
Thanks,
RK
Monday, December 10, 2012 2:08 PM -
SQL is really an inappropriate tool for such a transformation. If this is merely for presentation purposes, rather than updating the data as stored, then it it best done in the presentation layer where RegularExpressions analyzers are available. If this is part of a data-cleansing operation in loading a data warehouse, it is better done before the data is loaded into the DB, again where RegEx and such tools are available.
However, you will first have to spell out your requirments more precisely than "convert first letter of the word as Upper and remaining in Lower", as this example already violates the rule as provided:
LIONA IAN O'CONNOR -> Liona Ian O'Connor
due to retaining capitalization of the 'C' following the apostrophe."Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
Monday, December 10, 2012 2:10 PM -
I think it should be possible using recursive CTE also that may be put in a view.
For every expert, there is an equal and opposite expert. - Becker's Law
My blogMonday, December 10, 2012 2:10 PM