Link to home
Start Free TrialLog in
Avatar of jtomascak
jtomascak

asked on

Remove . from value

I need to replace the values in a table that have a value of xxx. with xxx (no decimal)
However if there is a number after the decimal I need to leave it alone.
Example  123. should become 123     but 123.4 should stay 123.4
The feild is type Char(9)
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
update tbl
set col = left(col,len(col)-1)
where rtrim(col) like '%.'
update tbl
set col = LEFT(col, CHARINDEX('.',col,0)-1) + RIGHT(col, (LEN(col) - CHARINDEX('.',col,0))
WHERE col like '%.'
forget my last post i just reread the question
Avatar of jtomascak
jtomascak

ASKER

That will do it.
this is what happen when you have a Network engineer playing DBA :)

thanks