需求:要填入以下值
去掉前面的0
SELECT
CASE
WHEN yfje >= 100000000 THEN CAST(FLOOR(yfje / 100000000) AS CHAR)
END AS yi,
CASE
WHEN (yfje % 100000000) / 10000000 >= 1 OR FLOOR(yfje / 10000000) > 0 THEN CAST(FLOOR((yfje % 100000000) / 10000000) AS CHAR)
END AS qianwan,
CASE
WHEN (yfje % 10000000) / 1000000 >= 1 OR FLOOR(yfje / 1000000) > 0 THEN CAST(FLOOR((yfje % 10000000) / 1000000) AS CHAR)
END AS baiwan,
CASE
WHEN (yfje % 1000000) / 100000 >= 1 OR FLOOR(yfje / 100000) > 0 THEN CAST(FLOOR((yfje % 1000000) / 100000) AS CHAR)
END AS shiwan,
CASE
WHEN (yfje % 100000) / 10000 >= 1 OR FLOOR(yfje / 10000) > 0 THEN CAST(FLOOR((yfje % 100000) / 10000) AS CHAR)
END AS wan,
CASE
WHEN (yfje % 10000) / 1000 >= 1 OR FLOOR(yfje / 1000) > 0 THEN CAST(FLOOR((yfje % 10000) / 1000) AS CHAR)
END AS qian,
CASE
WHEN (yfje % 1000) / 100 >= 1 OR FLOOR(yfje / 100) > 0 THEN CAST(FLOOR((yfje % 1000) / 100) AS CHAR)
END AS bai,
CASE
WHEN (yfje % 100) / 10 >= 1 OR FLOOR(yfje / 10) > 0 THEN CAST(FLOOR((yfje % 100) / 10) AS CHAR)
END AS shi,
CASE
WHEN (yfje % 10) / 1 >= 1 OR FLOOR(yfje / 1) > 0 THEN CAST(FLOOR((yfje % 10) / 1) AS CHAR)
END AS yuan,
FLOOR((yfje - FLOOR(yfje)) * 10) AS jiao,
FLOOR((yfje - FLOOR(yfje)) * 100) % 10 AS fen
FROM tht_htfk
效果图