How to change case in string

Question

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

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

Answer 1

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)

Answer 2

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

Answer 3

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 '\'

Answer 4

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'),'_','')

Answer 5

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