How to replace the strings?

  • 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

  • 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/

  • 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

  • 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

  • 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

  • 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/

  • 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

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

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