locked
Similar to Initcap in SQL server T-SQL RRS feed

  • 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 blog

    Monday, December 10, 2012 2:10 PM