Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: removing special characters

    I have a large table with a text field that includes line break characters. The line break characters are wreaking havoc on other applications, and they need to be removed and replaced with something more friendly. I found a referenced to a stored procedure that can search and replace all strings in a database, but it isn't quite what I need to do, since it only accepts valid keyboard input and not special characters.

    Anyone got any suggestions?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should be able to submit the ASCII value of the special character you want to target.

    blindman

  3. #3
    Join Date
    Oct 2003
    Posts
    12

    Re: removing special characters

    If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

    I.E.

    SELECT REPLACE(REPLACE(@StringToBeSearched,Char(10),' '),Char(13), '')

    Hope this helps..

  4. #4
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59

    Thumbs up

    can u post some example so that it can be tested and answered!!!!!!!!!!
    Cyrus
    Finding ways for solution

  5. #5
    Join Date
    Oct 2003
    Posts
    7

    Re: removing special characters

    Originally posted by smorton
    If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

    I.E.

    SELECT REPLACE(REPLACE(@StringToBeSearched,Char(10),' '),Char(13), '')

    Hope this helps..
    These are TEXT fields, unfortunately.

    Next question: if I can enter the ascii value of the character in the find/replace SP, how do I do so?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's what this code is doing for you:

    SELECT REPLACE(REPLACE(@StringToBeSearched,Char(10),' '),Char(13), '')

    The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
    Tab: CHAR(9)
    Line feed: CHAR(10)
    Carriage return: CHAR(13)

    If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

    SELECT REPLACE(REPLACE(Cast(@StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

    blindman

  7. #7
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by blindman
    That's what this code is doing for you:

    SELECT REPLACE(REPLACE(@StringToBeSearched,Char(10),' '),Char(13), '')

    The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
    Tab: CHAR(9)
    Line feed: CHAR(10)
    Carriage return: CHAR(13)

    If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

    SELECT REPLACE(REPLACE(Cast(@StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

    blindman
    I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rscrawford
    I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!
    Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?

    Access was (2002 may be don't know) as feature rich as sql server...

  9. #9
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by Brett Kaiser
    Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?
    You're right, of course. I duplicated the table in question.

    Now, forgive me for being a newbie, but... I have the SELECT statement from blindman:

    SELECT REPLACE(REPLACE(Cast(@StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')
    How do I execute this? I've opened the Query Analyzer, but I don't see where this statement allows for a FROM clause.

    Bear in mind, I'm not at all used to SQL Server. Normally, I'd spend a few hours researching on the web, but this particular piece has a priority to it.

  10. #10
    Join Date
    Oct 2003
    Posts
    12
    SELECT REPLACE(REPLACE(Cast(@StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')


    Replace @StringToBeSearched with the column name then add your from clause.

    I.E.

    SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
    FROM TableName t

    If your column is varchar or nvarchar you do not need to cast the column. If the column is text or ntext the above cast is valid.

  11. #11
    Join Date
    Oct 2003
    Posts
    7

    SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
    FROM TableName t
    Thank you very much!!! That did exactly what I needed it to do!

Posting Permissions

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