Home » SQL & PL/SQL » SQL & PL/SQL » Identifying Control Characters as well as viewing them.. (merged)
Identifying Control Characters as well as viewing them.. (merged) [message #358789] Wed, 12 November 2008 08:57 Go to next message
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 #358792 is a reply to message #358789] Wed, 12 November 2008 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an editor that allows you to see the control character, this has nothing to do with SQL or PL/SQL.

Regards
Michel

[Updated on: Wed, 12 November 2008 09:00]

Report message to a moderator

Re: Identifying Control Characters as well as viewing them.. [message #358794 is a reply to message #358792] Wed, 12 November 2008 09:03 Go to previous messageGo to next message
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 #358796 is a reply to message #358794] Wed, 12 November 2008 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Spool the result in a file and open it with vi or od for instance.

Or use DUMP on the column you want to test and see if some characters are not in the wanted code points.

Regards
Michel
Re: Identifying Control Characters as well as viewing them.. [message #358798 is a reply to message #358789] Wed, 12 November 2008 09:14 Go to previous messageGo to next message
maddyr
Messages: 16
Registered: November 2008
Junior Member
I will try doing that Michel...thanks...but then how will i eliminate those characters...i m especially concerned about the characters which are within two letters..i.e. within a word...if u can help me with a query for that...
Re: Identifying Control Characters as well as viewing them.. [message #358828 is a reply to message #358798] Wed, 12 November 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once you identified which ones they are, you can use REPLACE or TRANSLATE.

Regards
Michel
Re: Identifying Control Characters as well as viewing them.. [message #358888 is a reply to message #358798] Wed, 12 November 2008 19:00 Go to previous messageGo to next message
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

Re: Identifying Control Characters as well as viewing them.. [message #358913 is a reply to message #358789] Wed, 12 November 2008 23:53 Go to previous messageGo to next message
maddyr
Messages: 16
Registered: November 2008
Junior Member
can u please explain the above query in a bit more detailed fashion...sorry but could not get what you did....
Re: Identifying Control Characters as well as viewing them.. [message #358930 is a reply to message #358913] Thu, 13 November 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The query is:
select rownum, rowid, a1 from test_control
where regexp_like (a1, '[ [:cntrl:] ]');

without the space between square brackets, Forum formatter automically changes what is inside [[]] to a Wiki link.

Regards
Michel
Re: Identifying Control Characters as well as viewing them.. [message #359001 is a reply to message #358913] Thu, 13 November 2008 04:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
maddyr wrote on Thu, 13 November 2008 00:53
can u please explain the above query in a bit more detailed fashion...sorry but could not get what you did....

I corrected the formatting of [[:cntrl:]] that the forum appeared to change to a wiki link. The query should make more sense now.
Re: Identifying Control Characters as well as viewing them.. [message #359018 is a reply to message #359001] Thu, 13 November 2008 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
[OT] How did you do that, avoid the site to translate it to a wiki link?

Regards
Michel
Re: Identifying Control Characters as well as viewing them.. [message #359024 is a reply to message #359018] Thu, 13 November 2008 06:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I used "& # 5 8 ;" (without the quotes and no space between the characters) instead of the colon : itself.
printing ascii character numbers [message #359026 is a reply to message #358789] Thu, 13 November 2008 06:42 Go to previous messageGo to next message
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 #359028 is a reply to message #359026] Thu, 13 November 2008 06:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use ASCII to get the value for a single chr, or DUMP to get the values for a whole string.
Re: Identifying Control Characters as well as viewing them.. [message #359030 is a reply to message #359024] Thu, 13 November 2008 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Neat! Thumbs Up

Regards
Michel
Re: printing ascii character numbers [message #359031 is a reply to message #359026] Thu, 13 November 2008 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you start a new topic for a related question?

As for "i wrote a query to view the rows containing those characters" I think you should thank those who give you the query in the original topic into which I embed this one.

Regards
Michel
Re: printing ascii character numbers [message #359164 is a reply to message #359031] Thu, 13 November 2008 23:47 Go to previous messageGo to next message
maddyr
Messages: 16
Registered: November 2008
Junior Member
Hey Michel,

I accept my mistake and am sorry for it...I assure you this wont be repeated again...anyways thanks everyone for your interest in my last problem...Michel i still dont understand how will i print those control characters ascii no.??? i want a combined query which will first find those control characters as well as give me their ascii no. as well their position of occurence...hope u can help me with this...and once again sorry for starting a new topic... Smile
Re: printing ascii character numbers [message #359177 is a reply to message #359164] Fri, 14 November 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i still dont understand how will i print those control characters ascii no.???

We already told you twice you can use DUMP:
SQL> select 'michel', dump('michel') from dual;
'MICHE DUMP('MICHEL')
------ ------------------------------------
michel Typ=96 Len=6: 109,105,99,104,101,108

1 row selected.

Regards
Michel
Re: printing ascii character numbers [message #359204 is a reply to message #359177] Fri, 14 November 2008 02:22 Go to previous messageGo to next message
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 #359209 is a reply to message #359204] Fri, 14 November 2008 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You now have all the bricks to build this by yourself, try it and come back with what you did. You don't expect we will do the whole job for you, don't you?

Regards
Michel
Re: printing ascii character numbers [message #359222 is a reply to message #359209] Fri, 14 November 2008 03:24 Go to previous messageGo to next message
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 #359229 is a reply to message #359222] Fri, 14 November 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your procedure code?

Regards
Michel
Re: printing ascii character numbers [message #359257 is a reply to message #359222] Fri, 14 November 2008 07:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #359968 is a reply to message #359963] Wed, 19 November 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: printing ascii character numbers [message #359978 is a reply to message #359968] Wed, 19 November 2008 01:20 Go to previous messageGo to next message
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 #360028 is a reply to message #359963] Wed, 19 November 2008 05:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
maddyr wrote on Wed, 19 November 2008 01:15
when i tried it out it was giving problems...if u can help me out

What issues?

Your query doesn't match what I supplied above.
Re: printing ascii character numbers [message #360037 is a reply to message #360028] Wed, 19 November 2008 05:30 Go to previous messageGo to next message
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 #360043 is a reply to message #360037] Wed, 19 November 2008 05:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
How many rows in the table actually have control characters in them?

If there is a large amount, you'd probably be better off using a SUBSTR/INSTR.
Re: printing ascii character numbers [message #360051 is a reply to message #360043] Wed, 19 November 2008 06:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous message
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;
Previous Topic: How Chek numeric value true / false in sql
Next Topic: PROCEDURE FOR ALL_OBEJCTS
Goto Forum:
  


Current Time: Thu Dec 08 10:19:24 CST 2016

Total time taken to generate the page: 0.07797 seconds