Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    37

    Unanswered: Removing Junk Characters

    HI,
    i have a table and one of the column have junk characters in it,how can i remove the junk characters?.

    Eg : Employee
    Eid Ename
    1 a�
    2 �ddd

    how can i remove Junk characters and get only Enames from aboue table.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    DECLARE @ename VARCHAR(20)
    SET @Ename = 'a�'
    
    SELECT REPLACE(@Ename, '�', '')
    FROM MyTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2011
    Posts
    8
    You may need to determine the value that the character is before the REPLACE can be effective. You can use something like this to get that value:
    Code:
    declare @WeirdText varchar(20) = 'a�'
    
    select cast(@WeirdText as varbinary(100))
    From an inspection of the values displayed and a little knowledge of haxadecimal or an ASCII chart, you can use the REPLACE function. For example, suppose the values displayed are: 0x613F

    The 61 is the lower case 'a' and the 3F (hex) has a decimal value of 63.

    Code:
    select REPLACE(@WeirdText, CHAR(63), '')
    This would remove the odd character. Be aware that there may be multiple different weirdo characters in your string so you may need to iterate through this process.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey Shendricks,
    There's also the ASCII() function in SQL server
    Code:
    SELECT Ascii('�')
    Last edited by gvee; 03-16-12 at 20:59.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If this is an NVACHAR field, you may need to use the UNICODE() function. Can't be sure, since the original datatype, and/or code page is not supplied.

Posting Permissions

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