Adi Cohn-120898 (9/4/2012)

---

Do you always have space before the decimal point? If the answer is true you can use the replace function to replace ' .0' with ' 0.0'. Using your example the code would look like this:

select replace('"Qty: 1 | Desc: 1991 LOADRITE BOAT TRAILER | Value: .00 | Recovered Value: .00 | NCIC#: "', ' .0', ' 0.0')

select replace('"Qty: 1 | Desc: PROTUFF FLAT TRAILER GREY IN COLOR | Value: 1500.00 | Recovered Value: .00| NCIC#: "', ' .0', ' 0.0')

Adi

Mark Eckeard (9/4/2012)

---

If you need this added to a query where you don't always know the value, try this:

select replace(YourColumnNameHere, '.00', '0.00')

This worked when I tested it using this code:

with cteTable(TextString)
as
(
select 'Qty: 1 | Desc: 1991 LOADRITE BOAT TRAILER | Value: .00 | Recovered Value: .00 | NCIC#: '
)

select replace(TextString, '.00', '0.00') as TextString from cteTable

Mark

Charmer (9/4/2012)

---

I have already tried with this...but it did not work for me...I don't know why....it seems we need to leave a space before decimal point like Adi said...Since the column always has space in front of decimal point....

Sean Lange (9/4/2012)

---

You have been around here long enough to know that this doesn't help. What do you mean it doesn't work? Unexpected results? Syntax error? How about you toss up some details and we can help.