Home » SQL & PL/SQL » SQL & PL/SQL » Removing Junk Characters (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit)
| 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. 
Manu
|
|
|
|
|
|
| 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 #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
|
|
|
|
|
|
| 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:35But 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
|
|
|
|
| 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.
|
|
|
|
Goto Forum:
Current Time: Wed Aug 20 07:39:59 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
