john john - 5 months ago 10
SQL Question

How to remove decimal points in SQL. when you create a View

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


this is in View trying to combine multiple column in one
MHSE_ column has decimal.
my result now comes like:
13185.00000000 Dawn blvd
i want to result to come
13185 Dawn blvd
Note
MHSE_ = house number
MDIRECT = Apt A B etc
MSTRT = street name
MSTTYP = if its Road or Drive etc

Answer

cast and replace

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

and eventually trim of the zero

and in you case

(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