How to replace the strings?

Question

How to replace the strings?

Charmer

SSChampion

Points: 14903
More actions
September 4, 2012 at 5:53 am

#263806

Hi Friends,
I have a column called Description which contains values like
"Qty: 1 | Desc: 1991 LOADRITE BOAT TRAILER | Value: .00 | Recovered Value: .00 | NCIC#: "
"Qty: 1 | Desc: PROTUFF FLAT TRAILER GREY IN COLOR | Value: 1500.00 | Recovered Value: .00| NCIC#: "
In here you can see values like .00 ...i want to replace those values like 0.00
This is the kinda output that i need, so please give your ideas friends...
"Qty: 1 | Desc: 1991 LOADRITE BOAT TRAILER | Value: 0.00 | Recovered Value: 0.00| NCIC#: "
"Qty: 1 | Desc: PROTUFF FLAT TRAILER GREY IN COLOR | Value: 1500.00 | Recovered Value: 0.00| NCIC#: "
Thanks,
Charmer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 2

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 SSChampion Points: 14903 More actions · Answer 3

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

Yes Adi, there is always a space between the decimals...

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 4

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

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....

Thanks,
Charmer

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Charmer (9/4/2012)
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
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....

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.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Charmer SSChampion Points: 14903 More actions · Answer 6

Sean Lange (9/4/2012)
Charmer (9/4/2012)
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
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....
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.

Sorry Sean....The issue has been fixed. Adi's idea worked for me...

Thanks,
Charmer