How to change case in string

My table has one column that contain strings like: ” HRM_APPLICATION_DELAY_IN”

I want to perform bellow operations on each row on this column

  • TSQL Calculate week number of the month
  • Query trick - kind of unpivot
  • Is this an effective and efficient SQL Query or is there a better way?
  • SQLServer vs StateServer for ASP.NET Session State Performance
  • Bulk Import XML into SQL Server
  • SQL Server 2005— How can I detach & attach a DB and not create a T-Log File?
    1. convert to lower case
    2. remove underscore “_”
    3. change case (convert to upper case) of the character after the underscore like: ” hrm_Application_Delay_In”

    Need help for conversion. Thanks for advance

    5 Solutions collect form web for “How to change case in string”

    Here is a function to achieve it:

    create function f_test
    @a varchar(max)
    returns varchar(max)
      set @a = lower(@a)
      while @a LIKE '%\_%' ESCAPE '\'
        select @a = stuff(@a, v, 2, upper(substring(@a, v+1,1)))
        from (select charindex('_', @a) v) a
      return @a


    select dbo.f_test( HRM_APPLICATION_DELAY_IN')



    To update your table here is an example how to write the syntax with the function:

    UPDATE <yourtable>
    SET <yourcolumn> = dbo.f_test(col)
    WHERE <yourcolumn> LIKE '%\_%' ESCAPE '\'

    For a variable this is overkill, but I’m using this to demonstrate a pattern

    declare @str varchar(100) = 'HRM_APPLICATION_DELAY_IN';
    ;with c(one,last,rest) as (
      select cast(lower(left(@str,1)) as varchar(max)),
             left(@str,1), stuff(lower(@str),1,1,'')
      union all
      select one+case when last='_'
                 then upper(left(rest,1))
                 else left(rest,1) end,
             left(rest,1), stuff(rest,1,1,'')
      from c
      where rest > ''
    select max(one)
    from c;

    That can be extended to a column in a table

    -- Sample table
    declare @tbl table (
      id int identity not null primary key clustered,
      str varchar(100)
    insert @tbl values
    -- the query
    ;with c(id,one,last,rest) as (
      select id,cast(lower(left(str,1)) as varchar(max)),
             left(str,1), stuff(lower(str),1,1,'')
      from @tbl
      union all
      select id,one+case when last='_'
                 then upper(left(rest,1))
                 else left(rest,1) end,
             left(rest,1), stuff(rest,1,1,'')
      from c
      where rest > ''
    select id,max(one)
    from c
    group by id
    option (maxrecursion 0);
    -- result
    ID  COLUMN_1
    1   hrm_Application_Delay_In
    2   hrm_Application_Delay_Out
    3   _Hrm_Application_Delay_Out
    5   (null)
    6   abc<de_Fg>hi

    Bellow two steps can solve problem,as example i use sys.table.user can use any one

    declare @Ret varchar(8000), @RetVal varchar(8000), @i int, @count int = 1;
    declare @c varchar(10), @Text varchar(8000), @PrevCase varchar, @ModPrefix varchar(10);
    DECLARE @FileDataTable TABLE(TableName varchar(200))
    INSERT INTO @FileDataTable
    select name FROM sys.tables where object_name(object_id) not like 'sys%'  order by name
    SET @ModPrefix = 'Pur'
    DECLARE crsTablesTruncIns CURSOR 
    FOR select TableName FROM @FileDataTable
        OPEN crsTablesTruncIns
        FETCH NEXT FROM crsTablesTruncIns INTO @Text
        WHILE @@FETCH_STATUS = 0
                SET @RetVal = '';
                select @i=1, @Ret = '';
                   while (@i <= len(@Text))
                            SET @c = substring(@Text,@i,1)
                            --SET  @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c)       
                            IF(@PrevCase = '_' OR @i = 1)
                                SET  @Ret = UPPER(@c)
                                SET  @Ret = LOWER(@c)
                                       --@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
                            if(@c like '[a-zA-Z]')
                                SET  @RetVal = @RetVal +  @Ret    
                            if(@c = '_')
                                SET @PrevCase = '_'
                                SET @PrevCase = ''
                            SET @i = @i +1
                SET @RetVal = @ModPrefix + @RetVal
                print cast(@count as varchar) + ' ' + @RetVal 
                SET @count = @count + 1
                EXEC sp_RENAME @Text , @RetVal
                SET @RetVal = ''
        FETCH NEXT FROM crsTablesTruncIns INTO @Text
    CLOSE crsTablesTruncIns
    DEALLOCATE crsTablesTruncIns

    I’d like to show you my nice and simple solution. It uses Tally function to split the string by pattern, in our case by underscope. For understanding Tally functions, read this article.

    So, this is how my tally function looks like:

    CREATE FUNCTION [dbo].[tvf_xt_tally_split](      
         @String NVARCHAR(max)
        ,@Delim   CHAR(1))
            WITH Tally AS (SELECT top (select isnull(LEN(@String),100)) n = ROW_NUMBER() OVER(ORDER BY [name]) from master.dbo.syscolumns)      
                SELECT LTRIM(RTRIM(SUBSTRING(@Delim + @String + @Delim,N+1,CHARINDEX(@Delim,@Delim + @String + @Delim,N+1)-N-1))) Value, N as Ix
                FROM Tally
                WHERE N < LEN(@Delim + @String + @Delim)
                AND SUBSTRING(@Delim + @String + @Delim,N,1) = @Delim 

    This function returns a table, where each row represents part of string between @Delim (in our case between underscopes). Rest of the work is simple, just cobination of LEFT, RIGHT, LEN, UPPER and LOWER functions.

       declare @string varchar(max)
        set @string = ' HRM_APPLICATION_DELAY_IN'
        -- convert to lower case 
        set @string = LOWER(@string)
        declare @output varchar(max)
        -- build string 
        select @output = coalesce(@output + '_','') +
            UPPER(left(Value,1)) + RIGHT(Value, LEN(Value) - 1) 
                from dbo.tvf_xt_tally_split(@string, '_')
    -- lower first char
    select left(lower(@output),1) + RIGHT(@output, LEN(@output) - 1)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.