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)
    as
    begin
      set @a = lower(@a)
      while @a LIKE '%\_%' ESCAPE '\'
      begin
        select @a = stuff(@a, v, 2, upper(substring(@a, v+1,1)))
        from (select charindex('_', @a) v) a
      end
      return @a
    end
    

    Example:

    select dbo.f_test( HRM_APPLICATION_DELAY_IN')
    

    Result:

    hrmApplicationDelayIn
    

    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
      ('HRM_APPLICATION_DELAY_IN'),
      ('HRM_APPLICATION_DELAY_OUT'),
      ('_HRM_APPLICATION_DELAY_OUT'),
      (''),
      (null),
      ('abc<de_fg>hi');
    
    -- 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
    4   
    5   (null)
    6   abc<de_Fg>hi
    
    select 
    replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(replace(
    replace(replace(replace(replace(replace(replace(lower('HRM_APPLICATION_DELAY_IN'),'_a','A'),'_b','B'),'_c','C'),'_d','D'),'_e','E'),'_f','F'),
    '_g','G'),'_h','H'),'_i','I'),'_j','J'),'_k','K'),'_l','L'),
    '_m','M'),'_n','N'),'_o','O'),'_p','P'),'_q','Q'),'_r','R'),
    '_s','S'),'_t','T'),'_u','U'),'_v','V'),'_w','W'),'_x','X'),
    '_y','Y'),'_z','Z'),'_','')
    

    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
        BEGIN
    
    
                SET @RetVal = '';
    
                select @i=1, @Ret = '';
    
                   while (@i <= len(@Text))
                   begin
    
                            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)
                            ELSE
                                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 = '_'
                            else
                                SET @PrevCase = ''
    
                            SET @i = @i +1
    
                    end
    
    
                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
        END
    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))
    RETURNS  TABLE
    as
    return
        (
    
            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.