Skip to content
September 30, 2010 / windperson

幹了一件很蠢的事情…

某個SQL Server裡的table的SerialNO欄位是varchar(50),本來都是存0~9的阿拉伯數字編號,後來這個欄位需要加英文字母來標示更多的row entry,所以產報表的排序sql script改成如下…

(Wrong Version)

-- It will raise error if the SerialNO has none numeric value! --
SELECT
CASE 
    WHEN ISNUMERIC(RTRIM(LTRIM(SerialNO))) != 1 THEN RTRIM(LTRIM(SerialNO))
    ELSE CAST(SerialNO AS INT)
END AS SN
   , ProductName
FROM ProductBaseData

然後就一直跑出如下的錯誤:

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘A100’ to data type int.”

百思不得其解,不是已經用ISNUMERIC()檢查過,把能轉成數字的才CAST()嗎?Annoyed 難不成searched CASE expression有bug?!Confused smile

後來恍然大悟,CASE的傳回值是同型態的,可是我在這裡卻想要回傳的是不能轉換成intvarchar值,於是修改成正確版本如下:

(Correct Version)

SELECT
CASE 
    WHEN ISNUMERIC(SerialNO ) !=1 THEN -1 //Use maximum value of INT if you want alphabet value to be sorted last.
    ELSE CAST(SerialNO AS INT)
END AS SN
   , ProductName
FROM ProductBaseData

於是一切正常…

reference:

http://stackoverflow.com/questions/119730/how-do-i-sort-a-varchar-column-in-sql-server-that-contains-numbers

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: