Home » Developer & Programmer » Forms » Need to check space in varchar2 columns (oracle 10g)
Need to check space in varchar2 columns [message #340483] Wed, 13 August 2008 01:43 Go to next message
bsathishmca
Messages: 9
Registered: April 2006
Location: covai
Junior Member
Hi ,

I need to check whether a varchar2 column have space or not.


select a.mycol1,a.mycol2 from mytable a where (decode(length(a.mycol1),length(rtrim(a.mycol1)),'No') is NULL and a.prcs_dte='11-Aug-2008';

The above query only i have used.But i need to check for n number of varchar columns in same table.Can any one suggest me.

Note : I have only select permission on the tables.So i could not able to use user_tab_col.
Re: Need to check space in varchar2 columns [message #340500 is a reply to message #340483] Wed, 13 August 2008 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check regular expressions:
SQL> create table test (col varchar2(20));

Table created.

SQL> insert all
  2    into test values ('no_space_in_here')
  3    into test values ('we have 3 spaces')
  4    into test values ('only one_space')
  5  select * from dual;

3 rows created.

SQL> select col,
  2    case
  3      when regexp_instr(col, '[url=/wiki/:blank:]:blank:[/url]') > 0 then
  4        'Yes, there is at least one space in here'
  5      else
  6        'No, no space in this string'
  7    end space_detector
  8  from test;

COL                  SPACE_DETECTOR
-------------------- ----------------------------------------
no_space_in_here     No, no space in this string
we have 3 spaces     Yes, there is at least one space in here
only one_space       Yes, there is at least one space in here

SQL>
Re: Need to check space in varchar2 columns [message #340632 is a reply to message #340500] Wed, 13 August 2008 08:50 Go to previous messageGo to next message
sasipalarivattom
Messages: 118
Registered: June 2007
Location: Cochin ( INDIA )
Senior Member

Hi Littlefoot,

Smart Work...

I didn't get this part..
when regexp_instr(col, '[url=/wiki/:blank:]:blank:[/url]') > 0 then

Will you please explain this?


Thanks $ Regards
Sasi...
Re: Need to check space in varchar2 columns [message #340692 is a reply to message #340632] Wed, 13 August 2008 15:29 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, good question ... ./fa/1580/0/ I didn't check the result of copy-pasting my SQL*Plus session. I believe that it was OraFAQ Forum engine which "recognized" something special and decided to "fix" the code.

I apologize for not double checking the message text. I tried to do the same again, but the result was messy (again). Therefore, I'll attach the screenshot instead.

./fa/4796/0/
icon9.gif  Re: Need to check space in varchar2 columns [message #343059 is a reply to message #340692] Tue, 26 August 2008 02:30 Go to previous messageGo to next message
sasipalarivattom
Messages: 118
Registered: June 2007
Location: Cochin ( INDIA )
Senior Member

Hi little foot,

Is there something wrong with my database???
I didn't get the result.. Sad

./fa/4863/0/


Regards,
Sasi...
Re: Need to check space in varchar2 columns [message #344447 is a reply to message #343059] Fri, 29 August 2008 16:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Remove that "url wiki" junk, use query posted as a screenshot in the message #340692 and try again.
Re: Need to check space in varchar2 columns [message #344534 is a reply to message #344447] Sat, 30 August 2008 05:07 Go to previous message
sasipalarivattom
Messages: 118
Registered: June 2007
Location: Cochin ( INDIA )
Senior Member

Oh 'm sorry,
I was just copy pasting the first code.
"I will be a fool until I stop "copy paste" Mad "

It Works Fine......
./fa/4919/0/
  • Attachment: space.PNG
    (Size: 14.41KB, Downloaded 270 times)
Previous Topic: Icon Display
Next Topic: Date Time Probelm in Form
Goto Forum:
  


Current Time: Sun Dec 11 02:09:40 CST 2016

Total time taken to generate the page: 0.07878 seconds