如何更改字符串大小写(How to change case in string)

我的表有包含字符串一样一列” HRM_APPLICATION_DELAY_IN”

我想在此列的各行执行的操作波纹管

1. 转换成小写
2. 删除下划线“_”
3. 改变字符的情况下（转换为大写）下划线之后，如” hrm_Application_Delay_In”

需要帮助的转换。 感谢提前

Answer 1:

下面是一个函数来实现它：

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

例：

select dbo.f_test( HRM_APPLICATION_DELAY_IN')

结果：

hrmApplicationDelayIn

在此更新您的表是一个例子，如何写用函数的语法：

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

Answer 2:

对于一个变量，这是矫枉过正，但我​​使用这个演示模式

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;

这可以在表扩展到一列

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

Answer 3:

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 4:

波纹管的两个步骤可以解决问题，因为例如我使用sys.table.user可以使用任何一个

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 5:

我想告诉你我的美好和简单的解决方案。 它使用计数功能的模式，来分割字符串在我们的情况下，由underscope。 为了解理货功能，阅读这篇文章 。

所以，这是我的计数功能看起来像：

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 
         )
    )

这个函数返回一个表，其中每一行代表@Delim之间字符串的一部分（在我们的underscopes之间的情况下）。 工作休息很简单，只是左，右，LEN，上下功能cobination。

   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)

文章来源: How to change case in string