Home » SQL & PL/SQL » SQL & PL/SQL » Please help me!!! its urgent(challenge to all experts)
Please help me!!! its urgent(challenge to all experts) [message #382528] Thu, 22 January 2009 18:06 Go to next message
sandipgenius
Messages: 2
Registered: January 2009
Junior Member
I wish to display all columns of a table which contains trailing/leading spaces.please help.then i need all the columns to be displayed alongwith their table names which contains leading and trailing spaces???
Re: Please help me!!! its urgent(challenge to all experts) [message #382529 is a reply to message #382528] Thu, 22 January 2009 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.

> its urgent
Please explain why it is urgent for us to solve this problem for you.

[Updated on: Thu, 22 January 2009 18:30]

Report message to a moderator

Re: Please help me!!! its urgent(challenge to all experts) [message #382530 is a reply to message #382528] Thu, 22 January 2009 18:36 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi,

Please post what have tried so far and perhaps experts here can help you out with some problems encountered and tips to simplify your code.

Regards,
Wilbert
Re: Please help me!!! its urgent(challenge to all experts) [message #382531 is a reply to message #382529] Thu, 22 January 2009 18:45 Go to previous messageGo to next message
sandipgenius
Messages: 2
Registered: January 2009
Junior Member
for any user,how to find all columns containing data with trailing/leading spaces of a table.if for one table we can find the columns then how can we find for a user
Re: Please help me!!! its urgent(challenge to all experts) [message #382538 is a reply to message #382529] Thu, 22 January 2009 19:42 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi BlackSwan,

it is a goog old problem and i also tried to get solution but met with failure. For Example 'abc ' and 'def ' both are stored in different column. when i use to report both column it displays as 'abc def' instead of 'abc def ' I convinced my mind that this is the problem related to oracle XE and apex 3.1.2. If we use any BIP as reporting tool this problem get solved. If multiple spaces made as trailing or leading or between two words can be displayed as such, i am the happiest person and will totally avoid using another reporting tool like BIP and can be able to solve everything through apex reporting tool itself and layout and alingment can be effected very effectively and also will be very useful to effect printing in pre-printed stationary. if you need i can narate much more features on allowing multiple spaces whatsoever nature. ofcourse OP has made his request in different shape but concepts are one and the same
yours
dr.s.raghunathan

ps here itself i face problem. while i type i typed as abc and trailing three spaces and def and trailing three spaces. and required as abc three spaces def three spaces where as my typing is getting displayed as abc one space, def one space and report abc onespace def only. if this happens to this forum question itself, how can we get the solution

[Updated on: Thu, 22 January 2009 19:45]

Report message to a moderator

Re: Please help me!!! its urgent(challenge to all experts) [message #382543 is a reply to message #382538] Thu, 22 January 2009 20:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
dr.s.raghunathan wrote on Fri, 23 January 2009 02:42

ps here itself i face problem. while i type i typed as abc and trailing three spaces and def and trailing three spaces. and required as abc three spaces def three spaces where as my typing is getting displayed as abc one space, def one space and report abc onespace def only. if this happens to this forum question itself, how can we get the solution



I would have imagined after a few hundred post you could have figured out how CODE tags can be used in the forum to preserve spaces in the post:

one space :
abd def

two spaces:
abd  def

three spaces:
abd   def


The same should be true for APEX or any reporting tool where the result is displayed as HTML. When you want to preserve spaces either put everything in PRE tags or replace spaces with the Non-Breaking-Space entity NBSP
Re: Please help me!!! its urgent(challenge to all experts) [message #382544 is a reply to message #382528] Thu, 22 January 2009 20:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> if this happens to this forum question itself, how can we get the solution
The behavior you describe is expected results from "unformatted" HTML text.
The standard to which any browser must conform requires that "extra" space characters be eliminated.
The problem is strictly on the presentation side & does not have anything with how Oracle RDBMS operates.
If you include leading or trailing spaces in any field, Oracle will return EXACTLY what had been previously inputted to it.
If you do NOT want leading or trailing space characters in any field, then do NOT store them in the DB in the 1st place.

Garbage In, Garbage Out (GIGO)!
Re: Please help me!!! its urgent(challenge to all experts) [message #382557 is a reply to message #382528] Fri, 23 January 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please help me!!! its urgent

This is not allowed by forum guide. Please read OraFAQ Forum Guide.

Quote:
(challenge to all experts)

If you think this will pull experts towards your question and encourage them to answer, it is stupid. You just create disappointment with a beginner question and will put them away from your futur topics.

Regards
Michel
Re: Please help me!!! its urgent(challenge to all experts) [message #382741 is a reply to message #382531] Fri, 23 January 2009 20:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
This is a simple problem, not a challenging one. You need to learn to develop code step by step. I have provided some explanation and demonstration below, using partial code with ... where you need to figure out what to fill in those ... with.

Suppose you have some data like this:

SCOTT@orcl_11g> SELECT * FROM test_tab
  2  /

COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
neither
            leading
                      trailing
                                  both

SCOTT@orcl_11g> 


You can use the Oracle SUBSTR function to test whether the first or last character of a column value is a space.

SCOTT@orcl_11g> SELECT col2
  2  FROM   test_tab
  3  WHERE  SUBSTR (col2, ...) = ...
  4  OR     SUBSTR (col2, ...) = ...
  5  /

COL2
----------
 leading

SCOTT@orcl_11g>


You can use WHERE EXISTS to determine whether any such value exists in a column of a table, without selecting all of the values.

SCOTT@orcl_11g> SELECT 'Y'
  2  FROM   DUAL
  3  WHERE  EXISTS
  4  	    (SELECT *
  5  	     FROM   test_tab
  6  	     WHERE  SUBSTR (col2, ...) = ...
  7  	     OR     SUBSTR (col2, ...) = ...)
  8  /

'
-
Y

SCOTT@orcl_11g>


You can use some dynamic sql to create and execute the select above, using different table and column names selected from the ALL_TAB_COLUMNS data dictionary view. There are various ways that you could do this. For example, you could use an anonymous pl/sql block and loop through a cursor of table and column names and output using dbms_output. Or, as demonstrated below, you could create a function and pass the table and column names to the function, so that you can select from sql. You can add a where condition to limit it to one schema.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION lead_or_trail
  2    (...,
  3  	...)
  4    RETURN ...
  5  AS
  6    v_yes_or_no	 VARCHAR2 (1) := 'N';
  7  BEGIN
  8    EXECUTE IMMEDIATE
  9  	 'SELECT ''Y''
 10  	  FROM	 DUAL
 11  	  WHERE  EXISTS
 12  		   (SELECT *
 13  		    FROM ' || ... || '
 14  		    WHERE  SUBSTR (' || ... || ', 1, 1) = '' ''
 15  		    OR	   SUBSTR (' || ... || ', -1, 1) = '' '')'
 16    INTO v_yes_or_no;
 17    RETURN v_yes_or_no;
 18  END lead_or_trail;
 19  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT ..., ...
  2  FROM   all_tab_columns
  3  WHERE  ... -- limit to one schema
  4  AND    lead_or_trail (..., ...) = 'Y'
  5  /

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
TEST_TAB                       COL2
TEST_TAB                       COL3
TEST_TAB                       COL4

SCOTT@orcl_11g> 

Re: Please help me!!! its urgent(challenge to all experts) [message #383435 is a reply to message #382741] Wed, 28 January 2009 20:45 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Do the TRIM functions help?

SQL> create table temp1 (c1 varchar2(10))
  2  /

Table created.

SQL> 
SQL> insert into temp1 values ('neither');

1 row created.

SQL> insert into temp1 values (' leading');

1 row created.

SQL> insert into temp1 values ('trailing ');

1 row created.

SQL> insert into temp1 values (' both ');

1 row created.


SQL> select * from temp1;

C1
----------
neither
 leading
trailing
 both

SQL> select * from temp1 where c1 != ltrim(c1)  -- leading
  2  /

C1
----------
 leading
 both

SQL> select * from temp1 where c1 != rtrim(c1)  -- trailing
  2  /

C1
----------
trailing
 both

SQL> select * from temp1 where c1 != trim(c1) -- either leading or trailing
  2  /

C1
----------
 leading
trailing
 both

SQL> select * from temp1 where c1 != ltrim(c1) and c1 != rtrim(c1) -- both leading and trailing
  2  /

C1
----------
 both

SQL> 

Kevin
Previous Topic: How to bifurcate the data from single column
Next Topic: boolean in table creation
Goto Forum:
  


Current Time: Sat Dec 10 13:08:21 CST 2016

Total time taken to generate the page: 0.08758 seconds