Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Talking Unanswered: strip certain characters

    Can anyone tell me how I can strip certain chahrcters from a string

    I know I can use replace, but i don't think this is appropriate for what I want to do

    For example I have the string

    declare @text varchar (100)
    select @text = 'word1, word2 & word3'

    if i do a replace on the string like this
    select @text = 'replace(@text, ',', '')
    select @text = 'replace(@text, '&', '')

    I end up with the string
    select @text = 'word1 word2 word3'

    i.e. 2 spaces between word2 and word3

    What i want the string to look like is :
    select @text = 'word1 word2 word3'

    Is there a way i can check for more than one space + characters (\ / , &) in one go

    many thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Well, it's sort of one go, but not really.

    SELECT @text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@text,',', ''),'&',''),'/',''),'\',''),' ',' ')

    I'm sure I got that wrong, but you can play with it. Unfortunately, there's no way to whack thema ll at once that I know of? Anyone else? I'm sure Brett, blindman, Pat, or rd have a better solution for this.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, he could replace two characters at a time: replace([HisString], '& ', '')
    ...which would look less messy but would be less robust than your method.

    He might consider writing a function to do this to make the code more readable and consistent.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by plork123
    I end up with the string
    Code:
    select @text = 'word1 word2   word3'
    i.e. 2 spaces between word2 and word3
    First, no I don't know of another way.

    And second, I don't believe what you're saying is true....

    If you replace '&' with '', there shouldn't be a space.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    If you replace '&' with '', there shouldn't be a space.
    And we can't replace '' with anything else??
    SELECT REPLACE(REPLACE('ABC,DEF',',',''),'','.')

Posting Permissions

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