OraFAQ Forum: SQL & PL/SQL » Removing Junk Characters

Home » SQL & PL/SQL » SQL & PL/SQL » Removing Junk Characters (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Removing Junk Characters [message #623473]

Wed, 10 September 2014 08:52

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi,

In a table, I have few rows with junk characters. I identified them manually.

  SELECT DISTINCT a.pending_sts, DUMP (a.pending_sts), COUNT (*)
    FROM table_name a
GROUP BY a.pending_sts;

Now I have to remove them, attached shows the junk characters I am trying to remove.

When I am trying to include them in 'IN' clause, SQL giving me below error:

SQL> SELECT
  2          DISTINCT a.pending_sts, DUMP (a.pending_sts), COUNT (*)
  3      FROM table_name a
  4      WHERE a.pending_sts
  5      IN
  6     ('',
  7  'k',
  8  '_',
  9  ',
 10  '\',
 11  '',
 12  PuTTY'&',
 13  '$',
 14  '3',
 15  'K',
 16  "'
 17  ',"
 18  '',
 19  '',
 20  'F',
 21  '!',
 22  '2',
 23  'n',
 24  '
',
 25  '/',
 26  'M',
 27  'w',
 28  '',
 29  '%',
 30  '6',
 31  '',
 32  '',
 33  '`',
 34  '',
 35  '',
 36  '',
 37  '*',
 38  '?',
 39  'Z',
 40  '\',
 41  'D',
 42  '~',
 43  'b',
 44  'I',
 45  '+',
 46  '',
 47  '4',
 48  '',
 49  'x',
 50  '}',
 51  '',
 52  'B',
 53  'a',
 54  'q',
 55  '',
 56  'p',
 57  'g',
 58  '^',
 59  ']',
 60  '@',
 61  'f',
 62  'R',
 63  '',
 64  ',

SQL> 'i',
SP2-0042: unknown command "'i'," - rest of line ignored.
SQL> '1',
SP2-0042: unknown command "'1'," - rest of line ignored.
SQL> '5',
SP2-0042: unknown command "'5'," - rest of line ignored.
SQL> '3',
SP2-0042: unknown command "'3'," - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> '
      ',
SP2-0042: unknown command "'
                            '," - rest of line ignored.
SQL> 's',
SP2-0042: unknown command "'s'," - rest of line ignored.
SQL> ']',
SP2-0042: unknown command "']'," - rest of line ignored.
SQL> '
      ',
SP2-0042: unknown command "'
                            '," - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> '{',
'-',
SP2-0042: unknown command "'{'," - rest of line ignored.
SQL> SP2-0042: unknown command "'-'," - rest of line ignored.
SQL> '',
SP2-0042: unknown command "''," - rest of line ignored.
SQL> '
',
SP2-0042: unknown command "'," - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> '',
SP2-0042: unknown command "''," - rest of line ignored.
SQL> '7',
SP2-0042: unknown command "'7'," - rest of line ignored.
SQL> '>',
SP2-0042: unknown command "'>'," - rest of line ignored.
SQL> '',
SP2-0042: unknown command "''," - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> '8',
SP2-0042: unknown command "'8'," - rest of line ignored.
SQL> 'E',
SP2-0042: unknown command "'E'," - rest of line ignored.
SQL> 'A',
SP2-0042: unknown command "'A'," - rest of line ignored.
SQL> '#',
SP2-0042: unknown command "'#'," - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> '@',
SP2-0042: unknown command "'@'," - rest of line ignored.
SQL> 'v',
SP2-0042: unknown command "'v'," - rest of line ignored.
SQL> '~',
SP2-0042: unknown command "'~'," - rest of line ignored.
SQL> 'c',
SP2-0042: unknown command "'c'," - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> '',
SP2-0042: unknown command "''," - rest of line ignored.
SQL> '',
SP2-0042: unknown command "''," - rest of line ignored.
SQL> 'j',
SP2-0042: unknown command "'j'," - rest of line ignored.
SQL> '!',
SP2-0042: unknown command "'!'," - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> 'J',
SP2-0042: unknown command "'J'," - rest of line ignored.
SQL> '',
SP2-0042: unknown command "''," - rest of line ignored.
SQL> ',
' ',
'{',
'(',
'|',
'',
"'    ',"
'7',
'b',
'w',
'
',
'',
'e',
',
';',
'.',
'
',
'q',
'=',
'r',
'4',
't',
'"',
'N',
'n',
'}',
'`')
GROUP BY a.pending_sts;Quit(coredump)

Can you help me idea, how I can remove them. Confused

Manu

Report message to a moderator

Re: Removing Junk Characters [message #623475 is a reply to message #623473]

Wed, 10 September 2014 08:55

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Well, for one, that is not valid syntax. you have unbalanced single quotes in multiple lines.

Report message to a moderator

Re: Removing Junk Characters [message #623476 is a reply to message #623473]

Wed, 10 September 2014 08:58

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

I am thinking about something like below:

  SELECT DISTINCT a.pending_sts, DUMP (a.pending_sts), COUNT (*)
    FROM table_name a
   WHERE DUMP (a.pending_sts) IN
            ('Typ=96 Len=1: 129',
             'Typ=96 Len=1: 107',
             'Typ=96 Len=1: 95',
             'Typ=96 Len=1: 8',
             'Typ=96 Len=1: 92',
             'Typ=96 Len=1: 133',
             'Typ=96 Len=1: 38',
             'Typ=96 Len=1: 36',
             'Typ=96 Len=1: 51',
             'Typ=96 Len=1: 203',
             'Typ=96 Len=1: 13',
             'Typ=96 Len=1: 25',
             'Typ=96 Len=1: 144',
             'Typ=96 Len=1: 198',
             'Typ=96 Len=1: 161',
             'Typ=96 Len=1: 178',
             'Typ=96 Len=1: 110',
             'Typ=96 Len=1: 21',
             'Typ=96 Len=1: 47',
             'Typ=96 Len=1: 205',
             'Typ=96 Len=1: 119',
             'Typ=96 Len=1: 16',
             'Typ=96 Len=1: 165',
             'Typ=96 Len=1: 54',
             'Typ=96 Len=1: 130',
             'Typ=96 Len=1: 158',
             'Typ=96 Len=1: 96',
             'Typ=96 Len=1: 159',
             'Typ=96 Len=1: 148',
             'Typ=96 Len=1: 146',
             'Typ=96 Len=1: 170',
             'Typ=96 Len=1: 191',
             'Typ=96 Len=1: 218',
             'Typ=96 Len=1: 220',
             'Typ=96 Len=1: 196',
             'Typ=96 Len=1: 254',
             'Typ=96 Len=1: 226',
             'Typ=96 Len=1: 201',
             'Typ=96 Len=1: 43',
             'Typ=96 Len=1: 132',
             'Typ=96 Len=1: 52',
             'Typ=96 Len=1: 29',
             'Typ=96 Len=1: 248',
             'Typ=96 Len=1: 125',
             'Typ=96 Len=1: 17',
             'Typ=96 Len=1: 194',
             'Typ=96 Len=1: 225',
             'Typ=96 Len=1: 241',
             'Typ=96 Len=1: 15',
             'Typ=96 Len=1: 240',
             'Typ=96 Len=1: 231',
             'Typ=96 Len=1: 94',
             'Typ=96 Len=1: 93',
             'Typ=96 Len=1: 192',
             'Typ=96 Len=1: 230',
             'Typ=96 Len=1: 210',
             'Typ=96 Len=1: 26',
             'Typ=96 Len=1: 131',
             'Typ=96 Len=1: 105',
             'Typ=96 Len=1: 177',
             'Typ=96 Len=1: 53',
             'Typ=96 Len=1: 179',
             'Typ=96 Len=1: 139',
             'Typ=96 Len=1: 115',
             'Typ=96 Len=1: 221',
             'Typ=96 Len=1: 11',
             'Typ=96 Len=1: 123',
             'Typ=96 Len=1: 45',
             'Typ=96 Len=1: 18',
             'Typ=96 Len=1: 149',
             'Typ=96 Len=1: 143',
             'Typ=96 Len=1: 55',
             'Typ=96 Len=1: 190',
             'Typ=96 Len=1: 4',
             'Typ=96 Len=1: 184',
             'Typ=96 Len=1: 197',
             'Typ=96 Len=1: 193',
             'Typ=96 Len=1: 35',
             'Typ=96 Len=1: 64',
             'Typ=96 Len=1: 118',
             'Typ=96 Len=1: 126',
             'Typ=96 Len=1: 99',
             'Typ=96 Len=1: 19',
             'Typ=96 Len=1: 142',
             'Typ=96 Len=1: 234',
             'Typ=96 Len=1: 33',
             'Typ=96 Len=1: 202',
             'Typ=96 Len=1: 30',
             'Typ=96 Len=1: 28',
             'Typ=96 Len=1: 32',
             'Typ=96 Len=1: 251',
             'Typ=96 Len=1: 168',
             'Typ=96 Len=1: 124',
             'Typ=96 Len=1: 134',
             'Typ=96 Len=1: 9',
             'Typ=96 Len=1: 183',
             'Typ=96 Len=1: 98',
             'Typ=96 Len=1: 247',
             'Typ=96 Len=1: 27',
             'Typ=96 Len=1: 185',
             'Typ=96 Len=1: 147',
             'Typ=96 Len=1: 229',
             'Typ=96 Len=1: 255',
             'Typ=96 Len=1: 59',
             'Typ=96 Len=1: 174',
             'Typ=96 Len=1: 138',
             'Typ=96 Len=1: 113',
             'Typ=96 Len=1: 189',
             'Typ=96 Len=1: 114',
             'Typ=96 Len=1: 180',
             'Typ=96 Len=1: 244',
             'Typ=96 Len=1: 162',
             'Typ=96 Len=1: 206',
             'Typ=96 Len=1: 238',
             'Typ=96 Len=1: 253',
             'Typ=96 Len=1: 224',
             'Typ=96 Len=1: 39',
             'Typ=96 Len=1: 182',
             'Typ=96 Len=1: 117',
             'Typ=96 Len=1: 160',
             'Typ=96 Len=1: 212',
             'Typ=96 Len=1: 121',
             'Typ=96 Len=1: 12',
             'Typ=96 Len=1: 217',
             'Typ=96 Len=1: 102',
             'Typ=96 Len=1: 49',
             'Typ=96 Len=1: 42',
             'Typ=96 Len=1: 222',
             'Typ=96 Len=1: 46',
             'Typ=96 Len=1: 2',
             'Typ=96 Len=1: 239',
             'Typ=96 Len=1: 14',
             'Typ=96 Len=1: 101',
             'Typ=96 Len=1: 7',
             'Typ=96 Len=1: 50',
             'Typ=96 Len=1: 141',
             'Typ=96 Len=1: 214',
             'Typ=96 Len=1: 237',
             'Typ=96 Len=1: 62',
             'Typ=96 Len=1: 163',
             'Typ=96 Len=1: 103',
             'Typ=96 Len=1: 171',
             'Typ=96 Len=1: 187',
             'Typ=96 Len=1: 60',
             'Typ=96 Len=1: 156',
             'Typ=96 Len=1: 10',
             'Typ=96 Len=1: 164',
             'Typ=96 Len=1: 195',
             'Typ=96 Len=1: 188',
             'Typ=96 Len=1: 48',
             'Typ=96 Len=1: 219',
             'Typ=96 Len=1: 37',
             'Typ=96 Len=1: 213',
             'Typ=96 Len=1: 157',
             'Typ=96 Len=1: 100',
             'Typ=96 Len=1: 252',
             'Typ=96 Len=1: 246',
             'Typ=96 Len=1: 216',
             'Typ=96 Len=1: 140',
             'Typ=96 Len=1: 150',
             'Typ=96 Len=1: 249',
             'Typ=96 Len=1: 23',
             'Typ=96 Len=1: 151',
             'Typ=96 Len=1: 176',
             'Typ=96 Len=1: 3',
             'Typ=96 Len=1: 155',
             'Typ=96 Len=1: 152',
             'Typ=96 Len=1: 58',
             'Typ=96 Len=1: 20',
             'Typ=96 Len=1: 137',
             'Typ=96 Len=1: 109',
             'Typ=96 Len=1: 209',
             'Typ=96 Len=1: 63',
             'Typ=96 Len=1: 200',
             'Typ=96 Len=1: 120',
             'Typ=96 Len=1: 41',
             'Typ=96 Len=1: 91',
             'Typ=96 Len=1: 232',
             'Typ=96 Len=1: 175',
             'Typ=96 Len=1: 181',
             'Typ=96 Len=1: 57',
             'Typ=96 Len=1: 6',
             'Typ=96 Len=1: 243',
             'Typ=96 Len=1: 233',
             'Typ=96 Len=1: 204',
             'Typ=96 Len=1: 56',
             'Typ=96 Len=1: 166',
             'Typ=96 Len=1: 97',
             'Typ=96 Len=1: 154',
             'Typ=96 Len=1: 34',
             'Typ=96 Len=1: 207',
             'Typ=96 Len=1: 236',
             'Typ=96 Len=1: 153',
             'Typ=96 Len=1: 104',
             'Typ=96 Len=1: 242',
             'Typ=96 Len=1: 173',
             'Typ=96 Len=1: 40',
             'Typ=96 Len=1: 112',
             'Typ=96 Len=1: 245',
             'Typ=96 Len=1: 106',
             'Typ=96 Len=1: 199',
             'Typ=96 Len=1: 127',
             'Typ=96 Len=1: 135',
             'Typ=96 Len=1: 167',
             'Typ=96 Len=1: 169',
             'Typ=96 Len=1: 5',
             'Typ=96 Len=1: 228',
             'Typ=96 Len=1: 111',
             'Typ=96 Len=1: 211',
             'Typ=96 Len=1: 31',
             'Typ=96 Len=1: 186',
             'Typ=96 Len=1: 223',
             'Typ=96 Len=1: 122',
             'Typ=96 Len=1: 1',
             'Typ=96 Len=1: 172',
             'Typ=96 Len=1: 136',
             'Typ=96 Len=1: 116',
             'Typ=96 Len=1: 24',
             'Typ=96 Len=1: 250',
             'Typ=96 Len=1: 61',
             'Typ=96 Len=1: 208',
             'Typ=96 Len=1: 227',
             'Typ=96 Len=1: 235',
             'Typ=96 Len=1: 44',
             'Typ=96 Len=1: 145',
             'Typ=96 Len=1: 215')
GROUP BY a.pending_sts;

Will it be valid to compare like this?

Thanks,
Manu

[Updated on: Wed, 10 September 2014 08:59]

Report message to a moderator

Re: Removing Junk Characters [message #623478 is a reply to message #623473]

Wed, 10 September 2014 09:04

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you want just want to remove them them use UPDATE and TRANSLATE.

Report message to a moderator

Re: Removing Junk Characters [message #623480 is a reply to message #623476]

Wed, 10 September 2014 09:05

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Will it be valid to compare like this?

No.

Report message to a moderator

Re: Removing Junk Characters [message #623481 is a reply to message #623480]

Wed, 10 September 2014 09:12

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

SQL> SELECT '''' one_way, CHR(39) or_another FROM dual;
 
ONE_WAY OR_ANOTHER
------- ----------
'       '

Report message to a moderator

Re: Removing Junk Characters [message #623482 is a reply to message #623481]

Wed, 10 September 2014 09:35

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi Michel,

Thanks, but as the data is there in excel, I have to compare it with the data in table.

Cookiemonster,

I already tried CHR way.
What I thought to do, was to get corresponding CHR values:

SELECT DISTINCT a.pending_sts, DUMP (a.pending_sts), chr(a.pending_sts), COUNT (*)
    FROM table_name a
GROUP BY a.pending_sts;

ORA-01722: invalid number

And then compare the data in excel on the basis of DUMP values, then will make update statement using CHR values in where clause.

Update table_name a
 set a.pending_sts = NULL
where chr(a.pending_sts) in
 (values from the above query)

But as shown above, the select statement is giving below error:
ORA-01722: invalid number

Manu

Report message to a moderator

Re: Removing Junk Characters [message #623483 is a reply to message #623482]

Wed, 10 September 2014 09:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Thanks, but as the data is there in excel,
this data would be much more useful in an Oracle table; such as EXTERNAL TABLE.

Report message to a moderator

Re: Removing Junk Characters [message #623488 is a reply to message #623482]

Wed, 10 September 2014 10:09

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

manubatham20 wrote on Wed, 10 September 2014 15:35

But as shown above, the select statement is giving below error:
ORA-01722: invalid number

I'm not sure why you thought it would do anything else. chr takes a number parameter. pending_sts isn't a number. To get the number value you would use to pass to chr you have to use the function that works the other way - ascii.

SQL> SELECT ASCII('a'), CHR(97) FROM dual;
 
ASCII('A') CHR(97)
---------- -------
        97 a

Report message to a moderator

Re: Removing Junk Characters [message #623489 is a reply to message #623488]

Wed, 10 September 2014 10:23

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Geeeeee, sorry. I have to use ASCII.

I was too confident I never check it on dual before executing it on main table.

  SELECT DISTINCT a.pending_sts,
                  DUMP (a.pending_sts),
                  ASCII (a.pending_sts),
                  COUNT (*)
    FROM service_agreement a
GROUP BY a.pending_sts;

After getting ASCII values, I will compare dump values in excel and will take corresponding ASCII values, and then will set value to NULL for those ASCII values in table.

Thanks.

Report message to a moderator

Previous Topic:	Will Procedure compile
Next Topic:	Masking of Customer Data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 20 07:05:38 CDT 2025