none
capitalise the first letter of each word in a string in SQL Server. RRS feed

Answers

  • Hi Damo,

    Please see: http://www.sql-server-helper.com/functions/initcap.aspx

    It has a function:

    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

     


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, October 12, 2012 6:44 AM
    Moderator

All replies

  • if name is the field, when you query it write the query as

    select Ucase(left(name,1)) + right(name,len(name)-1) from tblname

    If you want to permanently do it, then write a stored procedure to do this.


    Regards, ManjunathRV http://manjunathrv.wordpress.com/

    Thursday, October 11, 2012 12:07 PM
  • Hi Damo,

    Please see: http://www.sql-server-helper.com/functions/initcap.aspx

    It has a function:

    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

     


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, October 12, 2012 6:44 AM
    Moderator
  • Use the below script 

     Select Upper(substring('ganesh' ,1 , 1 )) + substring('ganesh' ,2 ,LEN('ganesh'))


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. By ganeshk

    Friday, October 12, 2012 7:12 AM
  • Take a look at this blog post

    SQL Server Proper Case Function


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, October 15, 2012 3:21 AM
    Moderator
  • Normally, this should be handled by your ETL process.

    If you use SSIS, you can easily create an script components and use StrConv function with vbProperCase arguement. Like StrConv(Your_String_field, vbProperCase)

    You certainly can do by T-SQL as others provided but it all involve a loop or need to use some XML features.


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!



    Monday, October 15, 2012 3:56 AM
  • Create  FUNCTION udf_TitleCase (@InputString VARCHAR(4000) )
    RETURNS VARCHAR(4000)
    AS
    BEGIN
    DECLARE @Index INT
    DECLARE @Char CHAR(1)
    DECLARE @OutputString VARCHAR(255)
    SET @OutputString = LOWER(@InputString)
    SET @Index = 2
    SET @OutputString =
    STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
    WHILE @Index <= LEN(@InputString)
    BEGIN
    SET @Char = SUBSTRING(@InputString, @Index, 1)
    IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
    IF @Index + 1 <= LEN(@InputString)
    BEGIN
    IF @Char != ''''
    OR
    UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
    SET @OutputString =
    STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
    END
    SET @Index = @Index + 1
    END
    RETURN ISNULL(@OutputString,'')
    END
    • Proposed as answer by Srikanthd5 Monday, October 15, 2012 5:34 AM
    Monday, October 15, 2012 5:34 AM
  • Hello,

    TSQL is not the right place for converting the string/text to a PROPER format. You should either do it at your front-end or in your reporting tool. It is because, string operations in SQL are very costlier from performance point of view. 

    However, this could be achieved using the below T-Sql - 

    DECLARE @StrToConvertToProper AS VARCHAR(100) = ' hello, how are you? '
    
    --Trim the Text
    SET @StrToConvertToProper = LTRIM(RTRIM(@StrToConvertToProper))
    
    --Trimed Text
    --SELECT @StrToConvertToProper
    
    --Find the No. of Words in the Text
    DECLARE @WordCount AS INT
    SET @WordCount = LEN(@StrToConvertToProper) - LEN(REPLACE(@StrToConvertToProper,' ','')) + 1
    
    --Variable to track the space position
    DECLARE @LastSpacePosition AS INT = 0
    
    --Loop through all the words
    WHILE(@WordCount > 0)
      BEGIN
        --Set the Space Position
        SET @LastSpacePosition = CHARINDEX(' ',@StrToConvertToProper,@LastSpacePosition + 1)
        
        --Replace the Character
        SET @StrToConvertToProper = STUFF(@StrToConvertToProper,
                                          @LastSpacePosition + 1,
                                          1,
                                          UPPER(SUBSTRING(@StrToConvertToProper, @LastSpacePosition + 1, 1)))
        --Decrement the Loop counter                                      
        SET @WordCount = @WordCount - 1
      END
      
    SELECT @StrToConvertToProper ConvertedText  

    Hope, this helps!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Monday, October 15, 2012 5:37 AM
  • Hi,

    u can use code as ..

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

    Declare

    @Name Varchar(20)

    set

    @Name='subhash'

    SELECT

    STUFF(@Name,1,1,(UPPER(Substring(@Name,1,1))))

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

    If fetch column from table then ..

    SELECT

    Regards

    Subhash Chandra

    STUFF(ColumnName,1,1,(UPPER(Substring(ColumnName,1,1)))) as Name from  Table

    Monday, October 15, 2012 3:37 PM
  • If you need you need to do this for First and Last Name while concatenation you can do below:-

    UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, LEN(FirstName))) + ', '
         + UPPER(LEFT(LastName, 1)) + LOWER(SUBSTRING(LastName, 2, LEN(LastName)))
         CustomerName


    • Edited by K_Singh Wednesday, November 22, 2017 9:28 PM
    Wednesday, November 22, 2017 9:07 PM