Post reply

Removing a % character from char data so I can convert it to float

  • January 10, 2008 at 3:41 pm

    #180550

    I have some data that came to me as a data type varchar when it really should be a float. When I tried to convert it to float, I found that a handful of the rows have the % sign used in them as a character. I can't figure out how to replace/remove it because, obviously, it is the SQL wildcard character! :pinch:

    Anyone got any ideas?

  • January 10, 2008 at 3:45 pm

    #766915

    two of the same character in a row is the escape for any special character in sql:brackets, percents, single quotes, etc.

    replace(somecolumn,'%%','') to get rid of a percent sign, if it was a single quote, it looks wierd but it is start end single quotes, plus two in a row for a literal:

    replace(somecolumn,'''','')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 10, 2008 at 3:49 pm

    #766918

    select

    Replaced = replace(Mycol,'%','')

    from

    (

    Select MyCol = 'XXX%BB%KKK'union all

    Select MyCol = '%zzzKKK'union all

    Select MyCol = 'XXXDDDBBKKK'

    ) a

    where

    a.MyCol like '%^%%' escape '^'

    Results:

    Replaced

    ----------------------

    XXXBBKKK

    zzzKKK

    (2 row(s) affected)

  • January 10, 2008 at 3:51 pm

    #766920

    I'm properly chastized for talking without testing...

    my answer is appropraite for WHERE statements, but not a replace...

    LIKE '%%%%' would be where a percent sign exists in the data, for example.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 10, 2008 at 4:02 pm

    #766923

    Aaahhh, what a relief. It worked. Thank you both.

Viewing 5 posts - 1 through 5 (of 5 total)

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