[SQL]清掉資料前面的0及空字串
看了「Removing Leading Zeros From Column in Table」將一些結解方法整理說明一下。
先準備測試資料，如下，
USE tempdb
GO
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '0001'
UNION ALL
SELECT '000100'
UNION ALL
SELECT '100100'
UNION ALL
SELECT '000 0001'
UNION ALL
SELECT '00.001'
UNION ALL
SELECT '01.001'
UNION ALL
SELECT '000'
UNION ALL
SELECT 'abc'
UNION ALL
SELECT ' '
UNION ALL
SELECT '00ABC'
UNION ALL
SELECT '00AB C'
GO
-- Original data
SELECT *
FROM Table1
GO
作者解法，在運算式( Col1 + ' ') 中非0及空白的字元位置，然後開始往後取字串出來，如下，
SELECT Col1 AS before ,
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
FROM Table1
以上做法如果為000或是 的話，回傳值就會是0 (SELECT PATINDEX('%[^0 ]%', '000 '))，所以就無法處理到字串為000或是 的資料。
Rainmaker的解法是加上判斷，如果是0的話，就直接回傳字串字，如下，
SELECT Col1 AS before ,
CASE PATINDEX('%[^0 ]%', Col1 + ' ')
WHEN 0 THEN ''
ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
END AS after
FROM Table1
Harsh的解法1，直接將運算式從Col1 + ' '改成Col1 + 'a'就搞定我的Case When了，如下，
SELECT Col1 AS before ,
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + 'a'), LEN(Col1))
FROM Table1
Harsh的解法2，用RIGHT函數取代SUBSTRING函數也可，如下，
SELECT Col1 AS before ,
RIGHT(Col1, LEN(Col1) + 1 - PATINDEX('%[^0 ]%', Col1 + 'a'))
FROM Table1
Msg 536, Level 16, State 2, Line 1
Invalid length parameter passed to the RIGHT function.
以上做法，如果字串是空字串的話，就會有Exception，因為LEN(' ')出來的值是0，所以減掉 PATINDEX('%[^0 ]%', Col1 + 'a' ) 的值就會變成負數，就會出現錯誤。
SELECT LEN(' ') ,
PATINDEX('%[^0 ]%', ' ' + 'a') ,
LEN(' ') + 1 - PATINDEX('%[^0 ]%', ' ' + 'a')
iamAkashSingh的解法是，先將0換成空字串，然後再做LTRIM(去掉前置字元)，然後再把後面的字串補回0， 如下，
SELECT Col1 AS before ,
REPLACE(LTRIM(REPLACE(Col1, '0', ' ')), ' ', '0')
FROM Table1
以上的做法，如果後面有空字串的話，就會被變成了0
lucazav的解法是，是先將資料轉成FLOAT再轉成VARCHAR，如下，
SELECT Col1 AS before ,
CAST(CAST(REPLACE(T.Col1, ' ', '') AS FLOAT) AS VARCHAR(10)) AS after
FROM Table1 AS T
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
以上的做法，因為先轉成FLOAT，所以如果不是數值的資料，就會有Exception哦。
最後再把測試的TABLE DROP掉。
-- Clean up
DROP TABLE Table1
GO
整理這些作法，希望對大家有幫助，如果有其他的想法，也請讓筆者知道，謝謝。
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
