Identifying Control Characters as well as viewing them.. (merged) [message #358789] |
Wed, 12 November 2008 08:57  |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
I have been trying to write a PL/SQL query to indentify as well as view the control characters in the table. The characters can be between two words or even between two letters in a word..i saw some articles on eliminating those control characters but then i need to view them first. I was hoping if someone out here could help me out...
|
|
|
|
Re: Identifying Control Characters as well as viewing them.. [message #358794 is a reply to message #358792] |
Wed, 12 November 2008 09:03   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Really appreciate your help but then I need to view those characters when i query the data...besides i later need to eliminate them too...i just need to give proof to the client that his tables has got control characters in his data..and if u can name such editors which can help me view control characters, that would be an added help...thanks
|
|
|
|
|
|
Re: Identifying Control Characters as well as viewing them.. [message #358888 is a reply to message #358798] |
Wed, 12 November 2008 19:00   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
One way to quickly determine which rows are affected, you can use the following:
SQL> insert into test_control values ('Line 1 with tab'||chr(9)||'in it and then a return '||chr(10)||'for Line 2');
1 row created.
SQL> insert into test_control values ('Line with no control characters');
1 row created.
SQL> select rownum, a1 from test_control;
ROWNUM A1
---------- ----------------------------------------
1 Line 1 with tab in it and then a return
for Line 2
2 Line with no control characters
SQL> select rownum, rowid, a1 from test_control
2 where regexp_like (a1, '[[:cntrl:]]');
ROWNUM ROWID A1
---------- ------------------ ----------------------------------------
1 AAAMMTAAEAAAABoAAA Line 1 with tab in it and then a return
for Line 2
Edited [[:cntrl:]] above to correct forum formatting. I don't remember the forum changing those characters to a wiki link before ?
[Updated on: Thu, 13 November 2008 04:52] Report message to a moderator
|
|
|
|
|
|
|
|
printing ascii character numbers [message #359026 is a reply to message #358789] |
Thu, 13 November 2008 06:42   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey,
I want to write a query which will give the ascii character number as my output...i have a table in which i have certain unwanted control characters and some other special characters...i wrote a query to view the rows containing those characters...now i want output which shows the ascii no for that character as well as its position in the row after searching for those characters...can u please help me with a query for this...i have no clue how will i get the ascii no. for a particular character printed...
Thanks and Regards.
|
|
|
|
|
|
|
|
Re: printing ascii character numbers [message #359204 is a reply to message #359177] |
Fri, 14 November 2008 02:22   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey Michel,
I want to write a single query which will find the position of the control characters, remove the control characters and print the ascii no. for those characters...There can be multiple occurences of the control characters in the column...i guess now my problem would be much more clearer...Thanks a lot for your effort anyways....
Regards..
|
|
|
|
Re: printing ascii character numbers [message #359222 is a reply to message #359209] |
Fri, 14 November 2008 03:24   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey Michel,
Thanks for your continuous support till now...I already made a procedure which was doing the same thing...But my boss is insisting on a single query which will do all the three together...I am not getting how will a single query do the three tasks together...see if you can help me on it...anyways am trying my best...I am new to oracle, so i guess i will take time to understand it...Hope you can understand...Thanks anyways..
Regards
|
|
|
|
Re: printing ascii character numbers [message #359257 is a reply to message #359222] |
Fri, 14 November 2008 07:14   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You aren't going to get a single piece of SQL which will both print the control characters (a SELECT) and remove them (an UPDATE)
Here's a rough and ready way of looking atthe control characters:select regexp_instr('abcfer'||chr(7)||'asdr'||chr(10)||'sss','[url=/wiki/:cntrl:]:cntrl:[/url]',1,level)
,dump(regexp_substr('abcfer'||chr(7)||'asdr'||chr(10)||'sss','[url=/wiki/:cntrl:]:cntrl:[/url]',1,level))
from dual connect by level <= 100;
You can use REGEXP_REPLACE to get rid of them.
|
|
|
Re: printing ascii character numbers [message #359331 is a reply to message #359222] |
Fri, 14 November 2008 21:33   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
The following should allow you to determine the actual control character in the record and where it is located in the record:
SQL> create table test_control (id number, a1 varchar2(60));
Table created.
SQL> insert into test_control values
2 (1, 'Line with tab'||chr(9)||'and a return '||chr(10)||'for Line 2');
1 row created.
SQL> insert into test_control values
2 (2, 'Line with no control characters');
1 row created.
SQL> insert into test_control values
2 (3, 'Line with heading'||chr(1)||'and '||chr(27)||' escape character');
1 row created.
SQL> insert into test_control values
2 (4, 'Line with shift in '||chr(15)||'and shift out'||chr(14));
1 row created.
SQL> select * from test_control;
ID A1
---------- ----------------------------------------
1 Line with tab and a return
for Line 2
2 Line with no control characters
3 Line with headingand scape character
4 Line with shift in and shift out
SQL> select distinct id,
2 regexp_instr(a1, '[[:cntrl:]]', 1, level) cntrl_char_position,
3 ascii(regexp_substr(a1, '[[:cntrl:]]', 1, level)) cntrl_char
4 from test_control
5 where regexp_like (a1, '[[:cntrl:]]')
6 connect by regexp_substr(a1, '[[:cntrl:]]', 1, level) is not null
7 order by 1;
ID CNTRL_CHAR_POSITION CNTRL_CHAR
---------- ------------------- ----------
1 14 9
1 28 10
3 18 1
3 23 27
4 20 15
4 34 14
6 rows selected.
|
|
|
Re: printing ascii character numbers [message #359963 is a reply to message #359331] |
Wed, 19 November 2008 00:15   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
I tried the below query :
select emp_name,
regexp_instr(emp_name, ':cntrl:') cntrl_char_position,
ascii(regexp_substr(emp_name, ':cntrl:')) cntrl_char
from emp_item
where regexp_like (emp_name, ':cntrl:');
This gives me the control characters and their ascii no but just for their first occurence...(e.g : co*ba$in...then this just gives me the occurence of * and not $)..I have multiple control characters in one word. Also the database is also huge so speed is also a constraint...If someone can please help me with it...Hey ebrian i really want to thank you for your effort but then it was too complex for me to understand and when i tried it out it was giving problems...if u can help me out...Thanks anyways...
Regards,
(note: i tried ':cntrl:' but then it is auto formatted, so no issues with that..)
[Updated on: Wed, 19 November 2008 00:17] Report message to a moderator
|
|
|
|
Re: printing ascii character numbers [message #359978 is a reply to message #359968] |
Wed, 19 November 2008 01:20   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey Michel, Hope this is a better version...If u can help me out now...
I tried the following query:
SELECT emp_Name,
Regexp_instr(emp_Name,':cntrl:') cntrl_Char_Position,
Ascii(Regexp_substr(emp_Name,':cntrl:')) cntrl_Char
FROM emp_Item
WHERE Regexp_like(emp_Name,':cntrl:');
This gives me the control characters and their ascii no but just for their first occurence...(e.g : co*ba$in...then this just gives me the occurence of * and not $)..I have multiple control characters in one word. Also the database is also huge so speed is also a constraint...If someone can please help me with it...Hey ebrian i really want to thank you for your effort but then it was too complex for me to understand and when i tried it out it was giving problems...if u can help me out...Thanks anyways...
Regards,
(note: The square brackets have been removed in the formatter)
|
|
|
|
Re: printing ascii character numbers [message #360037 is a reply to message #360028] |
Wed, 19 November 2008 05:30   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey,
Thanks for your interest in my problem..I tried your query on our table..Actually the data is very huge...so it was taking a lot of time...I tried the query without using that connect by function..And honestly, I am not understanding your query as well...So i tried it in the form i showed..But then it returns the control character position and its ascii no of just the first occurence...Hope you can help me out with this...Thanks a lot anyways...
Regards,
|
|
|
|
Re: printing ascii character numbers [message #360051 is a reply to message #360043] |
Wed, 19 November 2008 06:16   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey,
Our table has close to around 10 million rows and when i execute the query you gave it keeps executing for hours...I need something faster and should pick up multiple occurences of the control characters within a word and print their ascii no as well...around 50 rows have control characters in them but then I will have to scan the whole column for that...
Regards,
|
|
|
Re: printing ascii character numbers [message #360064 is a reply to message #360051] |
Wed, 19 November 2008 07:44   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
ry something like this:
select distinct id,
regexp_instr(a1, '[url=/wiki/:cntrl:]:cntrl:[/url]', 1, level) cntrl_char_position,
ascii(regexp_substr(a1, '[url=/wiki/:cntrl:]:cntrl:[/url]', 1, level)) cntrl_char
from (select *
from test_control
where regexp_instr(a1,'[url=/wiki/:cntrl:]:cntrl:[/url]') > 0)
where regexp_like (a1, '[url=/wiki/:cntrl:]:cntrl:[/url]')
connect by regexp_substr(a1, '[url=/wiki/:cntrl:]:cntrl:[/url]', 1, level) is not null
order by 1;
This should restrict the xpensive part of the query to only those rows that contain control characters.
|
|
|
Re: printing ascii character numbers [message #360216 is a reply to message #360064] |
Thu, 20 November 2008 01:39   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey JRowbottom,
The query which you supplied is not working...As in its neither picking up control character nor it is providing any ascii values...Hope you can help me out with this...I need something which will pick multiple occurences of control character within a word as well as print their ascii no...Thanks anyways..
Regards,
|
|
|
Re: printing ascii character numbers [message #360223 is a reply to message #360216] |
Thu, 20 November 2008 02:02   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The query is great but there is a problem with forum display:
SQL> select * from test_control ;
ID A1
---------- ------------------------------------------------------------
1 Line with tab and a return
for Line 2
2 Line with no control characters
3 Line with headingand escape character
4 Line with shift in and shift out
4 rows selected.
SQL> select distinct id,
2 regexp_instr(a1, '[[:cntrl:]]', 1, level) cntrl_char_position,
3 ascii(regexp_substr(a1, '[[:cntrl:]]', 1, level)) cntrl_char
4 from (select *
5 from test_control
6 where regexp_instr(a1,'[[:cntrl:]]') > 0)
7 where regexp_like (a1, '[[:cntrl:]]')
8 connect by regexp_substr(a1, '[[:cntrl:]]', 1, level) is not null
9 order by 1;
ID CNTRL_CHAR_POSITION CNTRL_CHAR
---------- ------------------- ----------
1 14 9
1 28 10
3 18 1
3 23 27
4 20 15
4 34 14
6 rows selected.
Regards
Michel
[Updated on: Thu, 20 November 2008 02:03] Report message to a moderator
|
|
|
Re: printing ascii character numbers [message #360308 is a reply to message #360223] |
Thu, 20 November 2008 06:29   |
maddyr
Messages: 16 Registered: November 2008
|
Junior Member |
|
|
Hey Michel,
Thanks a lot for your answer...I tried it and it was running fine...Although speed was still a problem probably because of 'connect by' function...Anyways thanks a lot again...will get back to you if i have any other issues...I really appreciate all you guys for helping a newbie...I hope i can be in your league some day...Thanks again..
Regards,
|
|
|
Re: printing ascii character numbers [message #360320 is a reply to message #360223] |
Thu, 20 November 2008 07:03  |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
I believe the second WHERE predicate is superfluous ? I believe it can be reduced to:
select distinct id,
regexp_instr(a1, '[[:cntrl:]]', 1, level) cntrl_char_position,
ascii(regexp_substr(a1, '[[:cntrl:]]', 1, level)) cntrl_char
from (select *
from test_control
where regexp_instr(a1,'[[:cntrl:]]') > 0)
connect by regexp_substr(a1, '[[:cntrl:]]', 1, level) is not null
order by 1;
|
|
|