Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Post Unanswered: REPLACE Function for Bad Characters?

    I need to know if I can use the REPLACE function to remove bad characters from a data column. The data type is text and I would like to remove any comma's or quotes from the data. I assume that this would be the best way to remove these characters.

    I've not been able to find much information on the REPLACE function so I'm not sure if the data can be replaced in the same column or if the
    replacement data has to be placed in another column.

    Any insight into this matter would be appreciated.

    TechRick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    update yourtable
    set textcol = replace(replace(textcol,','),'''')

    if there is no replacement character, the search character is removed

    i'm pretty sure you can nest oracle functions (i can't test it because i don't gots no oracle database)

    to create a string consisting of a single quote, i think you have to code two consecutive ones, hence the four of them in a row like that

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35
    Thanks for the reply.

    I think there is a difference however in the SQL that I'm using verses yours. I'm running on SQL 2000 and doing my queries through the Query Analyzer.

    The format for the function command you suggested doesn't seem to work properly for me.

    In my SAM's Learn SQL in 21 Days book I have this format given:

    SELECT LASTNAME, REPLACE(LASTNAME, 'ST') REPLACEMENT
    FROM CHARACTERS;

    OUTPUT:
    LASTNAME REPLACEMENT
    --------------- ---------------
    CHRISTINE CHRIINE
    ADAMS ADAMS
    COSTALES COALES

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

    The problem I have with this is that it takes the replacement data and puts it into another column. I want to keep the updated data in the same column. I guess I may need to live with a 'replacement' column and disregard the original?

    Thanks again for your help.

    TechRick

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, you posted in the "SQL and PL/SQL" forum, you should've posted in the "Microsoft SQL/Server" forum

    i naturally assumed oracle, since REPLACE is an oracle function

    in sql.server, the replacement string is not optional, so you have to specify it as a zero-length string

    see http://msdn.microsoft.com/library/en...ra-rz_76lh.asp

    so try

    update yourtable
    set textcol = replace(replace(textcol,',',''),'''','')


  5. #5
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Smile

    Sorry for the confusion. I didn't realize I was in the wrong place.

    I'm a newbie to SQL and have only been using it for less than 2 weeks. I'm not yet familiar with all the differences in SQL formats.

    I'll try to find my way to the correct forum in the future.

    Thanks again for your help.

    TechRick

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, no problem, i just thought i'd mention why you got an oracle answer instead of an sql/server answer the first time (i am not the moderator of either of these two forums)

    so, did the sql/server version work?

  7. #7
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Thumbs up

    Rudy,

    I just finished testing it and sure enough it did the trick! Thanks much for all the help.

    One more question, why did you structure it this way:

    replace(replace(textcol,',',''),'''','')


    I thought it would need to be replace (texcol, ",", " ")

    Why the extra replace and the added ),'''','')?

    Just wondering.

    Thanks again,
    TechRick

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one removes commas, the other removes quotes

    nesting them passes the result of one into the other

    beats using two update stements, eh


    rudy

  9. #9
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35
    Very nice.

    I should have guessed that was the case.

    Thanks again for all your help.

    Best Regards,
    TechRick

  10. #10
    Join Date
    Aug 2002
    Posts
    3
    I think we can also do this using TRANSLATE Function. I tested this one only in ORACLE and not in SQL-Server. I dont know whether we have one similar funtion in SQL-Server.

    SELECT emp_id ,
    emp_name,
    TRANSLATE( emp_name, '%!@#$^', ' ' )
    FROM employee_table ;

    Regards,
    Sreekon.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •