john john - 5 months ago 10
MySQL 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), '.',''))),''))

with floor

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