mysql - 创建 View 时如何删除 SQL 中的小数点

Question

(IsNUll((CAST(MHSE_ as varchar (20))),'')  + ' ' + isnull(MDIRCT,'') + ' ' +  IsNull(MSTRT,'') + ' ' + isnull(MSTTYP,'')) as FULLADDRESS

这是在 View 中试图将多个列合并为一个MHSE_ 列有小数。

我的结果现在是这样的:

13185.00000000 Dawn blvd

我要结果来

13185 Dawn blvd

注意:

MHSE_ = house number 
MDIRECT = Apt A B etc 
MSTRT = street name 
MSTTYP = if its Road or Drive etc`

Answer 1

转换和替换

select REPLACE( cast( MHSE_ column AS CHAR), '.','') from your_view 

并最终修剪零

在你的情况下

(IsNUll(TRIM(LEADING '0' FROM (REPLACE( cast( MHSE_ column AS CHAR), '.',''))),''))

有地板

,(IsNUll((Cast(FLOOR(MHSE_) as CHAR (20))),'')  +
 ' ' + isnull(MDIRCT,'') + ' ' +  IsNull(MSTRT,'') + ' ' + 
        isnull(MSTTYP,'')) as FULLADDRESS