CREATE PROC TT (@OTF_OTFTNAME VARCHAR(100))
AS
BEGIN
SELECT
CASE WHEN ltrim(rtrim(@OTF_OTFTNAME)) like '% %'
THEN upper(left(@OTF_OTFTNAME,1)+ left(ltrim(stuff(@OTF_OTFTNAME,1,charindex(' ',@OTF_OTFTNAME),'')),1 ) )+'_ID'
WHEN ltrim(rtrim(@OTF_OTFTNAME)) like '%_%'
THEN upper(left(@OTF_OTFTNAME,1)+ left(ltrim(stuff(@OTF_OTFTNAME,1,charindex('_',@OTF_OTFTNAME),'')),1 ) )+'_ID'
ELSE Upper(left(@OTF_OTFTNAME ,2) ) END
END
EXEC TT @OTF_OTFTNAME='CHINA'
当有空格和下划线时都正常，当输入CHINA时应该显示CH_ID的，不知道为什么却显示CC_ID。
------解决方案--------------------
因为下划线在SQL属于其自己属性，如需要此处理，要加入[] 即可。
CREATE PROC TT (@OTF_OTFTNAME VARCHAR(100))
AS
BEGIN
SELECT
CASE WHEN ltrim(rtrim(@OTF_OTFTNAME)) like '% %'
THEN upper(left(@OTF_OTFTNAME,1)+ left(ltrim(stuff(@OTF_OTFTNAME,1,charindex(' ',@OTF_OTFTNAME),'')),1 ) )+'_ID'
WHEN ltrim(rtrim(@OTF_OTFTNAME)) like '%[_]%'
THEN upper(left(@OTF_OTFTNAME,1)+ left(ltrim(stuff(@OTF_OTFTNAME,1,charindex('_',@OTF_OTFTNAME),'')),1 ) )+'_ID'
ELSE Upper(left(@OTF_OTFTNAME ,2) ) END
END
EXEC TT @OTF_OTFTNAME='CHINA'