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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
update tbl
set col = LEFT(col, CHARINDEX('.',col,0)-1) + RIGHT(col, (LEN(col) - CHARINDEX('.',col,0))
WHERE col like '%.'
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
ASKER
That will do it.
this is what happen when you have a Network engineer playing DBA :)
thanks
this is what happen when you have a Network engineer playing DBA :)
thanks
set col = left(col,len(col)-1)
where rtrim(col) like '%.'