Sorting out capitalisation

  • Any advice appreciated please.

    In many of our reports, some text comes through in upper case, eg a name as:

    JOHN SMITH

    Or in lower case - john smith

    Is there any code / an expression we can use in our queries to allow regular capitalisation, so the data always appears (in a report) as:

    John Smith ?

  • Give this a try

    create function dbo.ProperCase (@name varchar(70))

    returns varchar(70)

    as

    begin

    declare @output varchar(70)

    declare @upper varchar(70)

    declare@space int

    declare @first varchar(70)

    declare @Last varchar(70);

    set @upper= rtrim(ltrim(upper(@name)))

    set @space= patindex('% %',@upper)

    set @first= left(@upper,@space)

    set @Last= reverse(left(reverse(@upper),@space) )

    set @output =

    left(@first,1)+lower(right(@first,len(@first)))

    +left(@last,1)+lower(right(@last,len(@last)-1))

    return @output;

    end

    go

    select dbo.ProperCase (' JOHN SMITH ')

    EDIT To handle superfluous spaces


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    β€”Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • This is a common topic and the functions have pros and cons.

    Here's a function by Jeff Moden that will run amazingly fast despite being a scalar function.

    CREATE FUNCTION [dbo].[InitialCap](@String [varchar](8000))

    RETURNS [varchar](8000) WITH EXECUTE AS CALLER

    AS

    BEGIN

    ----------------------------------------------------------------------------------------------------

    DECLARE @Position INT

    ;

    --===== Update the first character no matter what and then find the next postion that we

    -- need to update. The collation here is essential to making this so simple.

    -- A-z is equivalent to the slower A-Z

    SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,

    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

    ;

    --===== Do the same thing over and over until we run out of places to capitalize.

    -- Note the reason for the speed here is that ONLY places that need capitalization

    -- are even considered for @Position using the speed of PATINDEX.

    WHILE @Position > 0

    SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,

    @Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

    ;

    ----------------------------------------------------------------------------------------------------

    RETURN @String;

    END ;

    And here's a thread with multiple solutions and their test cases.

    http://www.sqlservercentral.com/Forums/Topic1531616-392-1.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks both for getting back. However your answers presume a technical knowledge that I do not currently possess!

    I have a field that is bringing through a business name into my report as, for example: GREAT CLOTHES

    I am looking for assistance with an expression, using UCase and LCase if possible, that will help express this as :

    Great Clothes

    Any suggestions appreciated - thanks.

  • We're giving you the code for the user defined functions(UDFs). There's no system function that will complete the problem easily and correctly.

    The code in the functions might be complex but you can get time to study it, go through every function to know what it does and understand each statement.

    You can use scalar UDFs as normal system scalar functions. For table valued functions (mentioned in the thread I linked to) you need to use CROSS APPLY. You can find examples in that thread in the posts that have the performance tests.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Presuming from your other post you're looking for a VB answer:

    =StrConv(Fields!FieldName.Value,vbProperCase)

    Note there's a Reporting Services section further down the page where you'll have more luck with VB, anything you post in this section will typically get a solution in T-SQL πŸ™‚

  • Thanks Gazareth, that solution worked perfectly. Also thanks for the tip re the correct forum.

  • Gazareth (11/13/2014)


    Presuming from your other post you're looking for a VB answer:

    =StrConv(Fields!FieldName.Value,vbProperCase)

    Note there's a Reporting Services section further down the page where you'll have more luck with VB, anything you post in this section will typically get a solution in T-SQL πŸ™‚

    Nice catch, I didn't realize that this was meant for SSRS. Knowing that, I might not have even tried to answer because my knowledge is very limited (I know how to spell it :hehe:).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Just for quick fun, here is an alternative approach, should be easy to turn it into an iTVF (SQL Server 2012 and later)

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @CAP_TEXT VARCHAR(50) = 'UPPER CASE STRING AND a lower case string';

    ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    , NUMS(N) AS (SELECT TOP(LEN(@CAP_TEXT)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5)

    ,WORD_SET AS

    (

    SELECT

    NM.N

    ,SUBSTRING(@CAP_TEXT,NM.N,1) AS T_CHAR

    ,SUM(CASE WHEN ASCII(SUBSTRING(@CAP_TEXT,NM.N,1)) < 65 THEN 1 ELSE 0 END) OVER

    (

    PARTITION BY (SELECT NULL)

    ORDER BY NM.N

    ROWS BETWEEN UNBOUNDED PRECEDING

    AND CURRENT ROW

    ) AS GRP_NO

    FROM NUMS NM

    )

    SELECT

    (

    SELECT

    CASE WHEN ROW_NUMBER() OVER

    (

    PARTITION BY WS.GRP_NO

    ORDER BY WS.N

    ) - SIGN(WS.GRP_NO) = 1 THEN UPPER(WS.T_CHAR)

    ELSE LOWER(WS.T_CHAR)

    END

    FROM WORD_SET WS

    FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(200)') AS PROPER_CASE

    ;

    Results

    PROPER_CASE

    ------------------------------------------

    Upper Case String And A Lower Case String

    Edit: added 2012 and later

  • faulknerwilliam2 (11/13/2014)


    Thanks Gazareth, that solution worked perfectly. Also thanks for the tip re the correct forum.

    No problem. Of course, if you're interested in learning T-SQL, this is definitely the right place πŸ™‚

  • Luis Cazares (11/13/2014)


    Gazareth (11/13/2014)


    Presuming from your other post you're looking for a VB answer:

    =StrConv(Fields!FieldName.Value,vbProperCase)

    Note there's a Reporting Services section further down the page where you'll have more luck with VB, anything you post in this section will typically get a solution in T-SQL πŸ™‚

    Nice catch, I didn't realize that this was meant for SSRS. Knowing that, I might not have even tried to answer because my knowledge is very limited (I know how to spell it :hehe:).

    Ha, I'd answered another couple of posts by the OP, so knew it was likely to be SSRS.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply