none
SSRS option to make capital first letter and rest small letters of a string

    Question

  • Hi,
    In the 'Name' field of String type is appearing all capital letters. I want to appear it as first letter is capital and rest all small. If there is space between two characters, then the second should appear as Caps again.

    Ex:
    1. PETER Should appear like Peter
    2. ALEXANDAR PETER Should appear as Alexander Peter

    Is it possible in SSRS or SQL Query ?
    I am using SQL Server 2005 SSRS Reporting Services.

    Thanks,

    • Edited by meemkay Thursday, July 09, 2009 8:43 AM
    Thursday, July 09, 2009 8:27 AM

Answers

  • Sure.  You could do this in VB or SQL.  Here's a quick exmaple of doing this using a VB function.  Open the Report Properties dialog and paste this code into the Code window:

    Function ProperCase(InputString as String) As String
             Dim i as Integer
             If InputString  <> "" Then
                Mid(InputString , 1, 1) = UCase(Mid(InputString , 1, 1))
                For i = 1 To Len(InputString) - 1
                   If Mid(InputString, i, 2) = Chr(13) + Chr(10) Then
                      Mid(InputString, i + 2, 1) = UCase(Mid(InputString, i + 2, 1))
                   End If
                   If Mid(InputString, i, 1) = " " Then
                      Mid(InputString, i + 1, 1) = UCase(Mid(InputString, i + 1, 1))
                   End If
                Next
                Return InputString
             End If
    End Function

    In a textbox, right-click.  Choose Expression... and type:

    =Code.ProperCase("the cow jumped over the moon.")

    If you want to apply this logic to a field, enter:

    =Code.ProperCase(Fields!SomeFieldName.Value)

    Preview the report.

    I hope this is helpful.

    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Thursday, July 09, 2009 9:19 AM
    Moderator
  • Strange, it works fine both with field and string. Could you create three columns for comparison, to see if there are some differences between three columns?

     

    =Code.ProperCase(LCase(Fields!SomeFieldName.Value))

    =Code.ProperCase(cstr(Fields!SomeFieldName.Value))

    = LCase(Fields!SomeFieldName.Value)

     

    Also, if this issue is caused by the field format, it will through warning message not the error message.

     

    Hope this helps.

    Raymond

    Monday, July 13, 2009 9:18 AM
    Moderator
  • Hi,
    It works with =Code.ProperCase( LCase (Fields!SomeFieldName.Value)).
    Thanks a lot.....


    • Marked as answer by meemkay Monday, July 13, 2009 12:26 PM
    Monday, July 13, 2009 10:10 AM

All replies

  • There is not RS option but rather .NET option. Write your own report custom code in VB.NET. It's not a hard problem.

    Thursday, July 09, 2009 9:17 AM
  • Sure.  You could do this in VB or SQL.  Here's a quick exmaple of doing this using a VB function.  Open the Report Properties dialog and paste this code into the Code window:

    Function ProperCase(InputString as String) As String
             Dim i as Integer
             If InputString  <> "" Then
                Mid(InputString , 1, 1) = UCase(Mid(InputString , 1, 1))
                For i = 1 To Len(InputString) - 1
                   If Mid(InputString, i, 2) = Chr(13) + Chr(10) Then
                      Mid(InputString, i + 2, 1) = UCase(Mid(InputString, i + 2, 1))
                   End If
                   If Mid(InputString, i, 1) = " " Then
                      Mid(InputString, i + 1, 1) = UCase(Mid(InputString, i + 1, 1))
                   End If
                Next
                Return InputString
             End If
    End Function

    In a textbox, right-click.  Choose Expression... and type:

    =Code.ProperCase("the cow jumped over the moon.")

    If you want to apply this logic to a field, enter:

    =Code.ProperCase(Fields!SomeFieldName.Value)

    Preview the report.

    I hope this is helpful.

    Paul Turley [Hitachi Consulting] SQLServerBIBlog.com
    Thursday, July 09, 2009 9:19 AM
    Moderator
  • Hi,
    When I type  '=Code.ProperCase(Fields!<ColumnName>.Value)' in Expression editor, there is no change in the result

    Thanks,

    Thursday, July 09, 2009 9:47 AM
  • Hi Meemkay,

     

    Using Custom Code as Paul mentioned to achieve that is a good choice, it works fine in my case. Did you get some warning message? Or when you type the expression like this: =Code.ProperCase("the cow jumped over the moon."), can you get the respected result?

     

    Regards,

    Raymond

    Monday, July 13, 2009 2:11 AM
    Moderator
  • Hi,
    Yeah...... it works when i place in the expression editor like this: =Code.ProperCase("the cow jumped over the moon."),but when i place =Code.ProperCase(Fields!SomeFieldName.Value), it doesn't work.
    Note: It does not fetch any error messege, indeed the result set is not affected at all.

    Thanks,

    Monday, July 13, 2009 8:54 AM
  • Strange, it works fine both with field and string. Could you create three columns for comparison, to see if there are some differences between three columns?

     

    =Code.ProperCase(LCase(Fields!SomeFieldName.Value))

    =Code.ProperCase(cstr(Fields!SomeFieldName.Value))

    = LCase(Fields!SomeFieldName.Value)

     

    Also, if this issue is caused by the field format, it will through warning message not the error message.

     

    Hope this helps.

    Raymond

    Monday, July 13, 2009 9:18 AM
    Moderator
  • Hi,
    It works with =Code.ProperCase( LCase (Fields!SomeFieldName.Value)).
    Thanks a lot.....


    • Marked as answer by meemkay Monday, July 13, 2009 12:26 PM
    Monday, July 13, 2009 10:10 AM
  • meemkay, have you seen in the code above the characters are being set to upper case?

    So you will probably have to do something like:

    =Code.ProperCase(LCase(Fields!SomeFieldName.Value))

    If you source string is all uppercase
    Tuesday, July 14, 2009 1:36 PM
  • Gracias. me sirvio de mucha ayuda
    Thursday, September 09, 2010 5:58 PM
  • I use this with no custom code in the Code window.  Much easier.

    =strConv(Fields!CUST_NAME.Value,vbProperCase)

     

    • Proposed as answer by Smithochris Monday, December 20, 2010 6:29 PM
    Thursday, September 09, 2010 8:27 PM
  • Well i faced the same issue, but i used ASCII conversion for expression and trim. example for displaying Dear Melinda, This is what i used,  =Chr(68)+Chr(101)+Chr(97)+Chr(114)+Chr(160)+Left(Fields!first_name.Value,1)+RTrim(LCase(Right(Fields!first_name.Value,Len(Fields!first_name.Value)-1)))+Chr(44).

    You can find the ASCII code values here,

    http://www.web-source.net/symbols.htm

    Best of luck. :)


    dinesh
    Tuesday, July 26, 2011 9:48 PM
  • The above code some times it is working and some times it shows #error why?

    suppose ex: name='Narendra Babu   '

     the above example it shows #error

    Friday, January 20, 2012 8:50 AM
  • Hi,

    Check with StrConv function:

    =StrConv("NAREndra Babu   ", vbProperCase,NOTHING)
    



    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Friday, January 20, 2012 8:58 AM
  • It is correctly working Thank Q
    Friday, January 20, 2012 8:58 AM
  • I use this with no custom code in the Code window.  Much easier.

    =strConv(Fields!CUST_NAME.Value,vbProperCase)

     


    It is working
    Friday, January 20, 2012 10:49 AM
  • its working
    Monday, August 04, 2014 6:20 AM
  • I use this with no custom code in the Code window.  Much easier.

    =strConv(Fields!CUST_NAME.Value,vbProperCase)

     

    This is a great answer, thank you. 
    Wednesday, December 07, 2016 8:43 PM