大小寫轉換的問題

[dofly]

恭祝大家新年好!

下面的SQL語句執行的時候，在區分字母的大小寫時發生錯誤，遇到字母時按前面的 when ... then 轉換，沒有區分大小寫，不是按
when 'a' then 'aaaaa' When 'A' then 'AAAAAAAA' 這樣轉換，
而是 when 'a' then 'aaa' when 'A' then 'aaa' 這樣子轉換了，

請指導一下，謝謝：

SELECT OITNR, SUBSTRING(ITNR,1,LEN(ITNR)-1)+
(CASE RIGHT(ITNR,1)
when '1' then '01' when '2' then '02' when '3' then '03' when '4' then '04' when '5' then '05' when '6' then '06' when '7' then '07' when '8' then '08' when '9' then '09'
when 'a' then '0a' when 'b' then '0b' when 'd' then '0d' when 'e' then '0e' when 'f' then '0f' when 'g' then '0g' when 'h' then '0h' when 'i' then '0i'
when 'j' then '0j' when 'k' then '1A'
WHEN 'A' THEN '0A' WHEN 'B' THEN '0B' WHEN 'C' THEN '0C' WHEN 'D' THEN '0D' WHEN 'E' THEN '0E' WHEN 'F' THEN '0F' WHEN 'G' THEN '0G' WHEN 'H' THEN '0H'
WHEN 'I' THEN '0I' WHEN 'J' THEN '0J' WHEN 'K' THEN '0K' WHEN 'L' THEN '0L' WHEN 'M' THEN '0M' WHEN 'N' THEN '0N' WHEN 'O' THEN '0O' WHEN 'P' THEN '0P' WHEN 'Q' THEN '0Q'
WHEN 'R' THEN '0R' WHEN 'S' THEN '0S' WHEN 'T' THEN '0T' WHEN 'U' THEN '0U' WHEN 'V' THEN '0V' WHEN 'W' THEN '0W' WHEN 'X' THEN '0X' WHEN 'Y' THEN '0Y'
WHEN 'Z' THEN '0Z'
END ) ITNR,
SPEC, TRB, ETDT, SORT, CTNI, TR1, TR2, MODT, TRSAY1, TRSAY2,
TRSAY3, TF, KAID, OK, BUYID, MZT, TRAKEY, P_NAME, P_ID, P_FLAG, LISTNO
FROM HUAA1301
WHERE SUBSTRING(ITNR,1,2) IN ('PB','SB','NB','AB','OB','BB','EB','FB','CB','LB','NB')

[xluzhong]

参考：
http://community.csdn.net/Expert/Forum.asp?url=/Expert/member/MyForum.asp?typenum=3&frame=true

[xluzhong]

参考：
http://community.csdn.net/Expert/Forum.asp?url=/Expert/member/MyForum.asp?typenum=3&frame=true

[xluzhong]

http://blog.csdn.net/xluzhong/articles/262098.aspx

[子陌红尘]

SELECT
OITNR,
SUBSTRING(ITNR,1,LEN(ITNR)-1)+
( CASE ASCII(RIGHT(ITNR,1))
when 107 then '1A'
else '0' + CHAR(ASCII(RIGHT(ITNR,1))
END ) ITNR,
SPEC,
TRB,
ETDT,
SORT,
CTNI,
TR1,
TR2,
MODT,
TRSAY1,
TRSAY2,
TRSAY3,
TF,
KAID,
OK,
BUYID,
MZT,
TRAKEY,
P_NAME,
P_ID,
P_FLAG,
LISTNO
FROM
HUAA1301
WHERE
SUBSTRING(ITNR,1,2) IN ('PB','SB','NB','AB','OB','BB','EB','FB','CB','LB','NB')

[xluzhong]

搂主，不好意思！

[子陌红尘]

少了个括号：

SELECT
OITNR,
SUBSTRING(ITNR,1,LEN(ITNR)-1)+
(CASE ASCII(RIGHT(ITNR,1))
WHEN 107 THEN '1A'
ELSE '0' + CHAR(ASCII(RIGHT(ITNR,1)))
END) ITNR,
SPEC,
TRB,
ETDT,
SORT,
CTNI,
TR1,
TR2,
MODT,
TRSAY1,
TRSAY2,
TRSAY3,
TF,
KAID,
OK,
BUYID,
MZT,
TRAKEY,
P_NAME,
P_ID,
P_FLAG,
LISTNO
FROM
HUAA1301
WHERE
SUBSTRING(ITNR,1,2) IN ('PB','SB','NB','AB','OB','BB','EB','FB','CB','LB','NB')