Solved

Scenario: Column with data including the Space - ASCII character

Posted on 2014-01-21
15
469 Views
Last Modified: 2014-01-21
Scenario

I have 10 tables and each table has 10 columns and each column is of datatype varchar2. Data in these columns could be including spaces. Just wanted to stress here, trim cannot be used here. This is an ASCII character. I will have to return column name and table name which has columns with data with space - ASCII character at the end of varchar2 ..

Could you please suggest how I do that..

Quick reply is appreciated..
0
Comment
Question by:d27m11y
  • 9
  • 5
15 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Why not use TRIM?  Or at least RTRIM?


if    (a != rtrim(a) or rtrim(a) is null ) then
     a ends in a space

seems like a valid test to me.  Is this homework with an arbitrary rule thrown in to force you to think outside the box?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
these conditions would also work

substr(a,-1) = ' '

regexp_like(a,' $')

instr(a,' ',-1) = length(a)

LIKE '% '
0
 
LVL 13

Expert Comment

by:magarity
Comment Utility
all tab columns is meant for this kind of thing.  Just replace 'mytables' with a list of your tables.  Grab the results, remove the UNION ALL from the first one and run the whole thing.  Note that you specify that all the fields in question are varchars.  If this script hits non-varchar fields, it may error:

SELECT DISTINCT
          'UNION ALL SELECT DISTINCT '' '
       || TABLE_NAME
       || ''' , '' '
       || COLUMN_NAME
       || ' '' FROM '
       || TABLE_NAME
       || ' WHERE '
       || COLUMN_NAME
       || ' LIKE ''% %'' '
  FROM all_tab_columns;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
This will tell you which tables and columns and how many rows have trailing spaces in each

SELECT table_name, column_name, cnt
  FROM (SELECT table_name,
               column_name,
               ROWNUM,
               TO_NUMBER(
                   EXTRACTVALUE(
                       DBMS_XMLGEN.getxmltype(
                              'select count(*) X from '
                           || table_name
                           || ' where substr('
                           || column_name
                           || ',-1) = '' '''
                       ),
                       '//X'
                   )
               )
                   cnt
          FROM all_tab_cols
         WHERE data_type ='VARCHAR2'
        -- add other conditions like table names or schema owner
)
 WHERE cnt > 0
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Note in 39798659

this clause will cause the queries to look for columns that have a space anywhere in the column, not just at the beginning


 ' LIKE ''% %'' '



use either the RTRIM in the first post, or any of the conditions in the second post to correct this
0
 

Author Comment

by:d27m11y
Comment Utility
I was given this scenario to identify the table and column which has spaces at the end and return the table name and column name.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Done,  see above: http:#a39798666  it doesn't even use RTRIM  (but it could)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:d27m11y
Comment Utility
Could you please suggest me how I can pass the table name and columnname as the parameter since I have ten tables here with such columns with ASCII characters.. Can we do this thru procedure/function..

QUick reply is appreciated.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
SELECT table_name, column_name, cnt
  FROM (SELECT table_name,
               column_name,
               ROWNUM,
               TO_NUMBER(
                   EXTRACTVALUE(
                       DBMS_XMLGEN.getxmltype(
                              'select count(*) X from '
                           || table_name
                           || ' where substr('
                           || column_name
                           || ',-1) = '' '''
                       ),
                       '//X'
                   )
               )
                   cnt
          FROM all_tab_cols
         WHERE data_type = 'VARCHAR2'
           AND table_name IN ('TABLE1',
                              'TABLE2',
                              'TABLE3',
                              'TABLE4',
                              'TABLE5',
                              'TABLE6',
                              'TABLE7',
                              'TABLE8',
                              'TABLE9',
                              'TABLE10'))
 WHERE cnt > 0;


change TABLE1-TABLE10 to your table names

you don't need to pass in the column names,  the query already searches for them.

however, if you wanted to list them explicitly, just add them in a list like this after the table condition

AND column_name in ('COLUMN1','COLUMN2','COLUMN3')
0
 

Author Comment

by:d27m11y
Comment Utility
thank you for your help.  I donot have same names for the columns in all tables. Am confused how to pass column names in the above query.  Since each table has 10 columns and the column names are not consistent across all the tables, could you please suggest in detail.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
>>> I donot have same names for the columns in all tables

that's fine

>>> Am confused how to pass column names in the above query.

what else do you need?  Just use

AND column_name in ('COLUMN1','COLUMN2','COLUMN3')   as shown previously


SELECT table_name, column_name, cnt
  FROM (SELECT table_name,
               column_name,
               ROWNUM,
               TO_NUMBER(
                   EXTRACTVALUE(
                       DBMS_XMLGEN.getxmltype(
                              'select count(*) X from '
                           || table_name
                           || ' where substr('
                           || column_name
                           || ',-1) = '' '''
                       ),
                       '//X'
                   )
               )
                   cnt
          FROM all_tab_cols
         WHERE data_type = 'VARCHAR2'
           AND table_name IN ('TABLE1',
                              'TABLE2',
                              'TABLE3',
                              'TABLE4',
                              'TABLE5',
                              'TABLE6',
                              'TABLE7',
                              'TABLE8',
                              'TABLE9',
                              'TABLE10')
AND column_name in ('COLUMN1','COLUMN2','COLUMN3')
)
 WHERE cnt > 0;



If this doesn't work, please post table and column names that are either missed or erroneously reported.

The only problem I could see with the above is if TABLE1 and TABLE2 both have a column X but you only want to check it in TABLE1 but not in TABLE2.

If that's the case you can build an in list of all 100 combinations

AND (table_name,column_name) in ((a,b), (c,d), (e,f), (g,h))  etc

where a,c,e,g  were table names,
b,d,f,g were columns from the corresponding table they were paired with

as you can see this is more complicated, much bigger if you try to do all 100 combinations and TOTALLY UNNECESSARY if the column names are different
0
 

Author Comment

by:d27m11y
Comment Utility
Thank you for your help again.

Since each table has 10 different columns and there are 10 such tables, do I have to include all 100 columns there as
 column_name in ('COLUMN1','COLUMN2','COLUMN3',....... 'COLUMN100') .

Is there any other alternative way
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if you have 100 different column names then yes.

But... you don't need to list them at all if you ONLY have those 100 columns in those 10 tables.

Does that make sense?

For example...  

Pick all people in this thread whose username ends in "stuber"

Now, pick all people in this thread whose username starts with "sd"

note, adding the extra condition didn't change the results.

So, are your table names like that?  If you list just those 10, will it be sufficient?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
oh, and if you're not sure, just run the table/column portion of the query without the space check.  Tune this query until it produces the list you want, then copy these conditions to the bigger query

select table_name,column_name
  FROM all_tab_cols
         WHERE data_type = 'VARCHAR2'
           AND table_name IN ('TABLE1',
                              'TABLE2',
                              'TABLE3',
                              'TABLE4',
                              'TABLE5',
                              'TABLE6',
                              'TABLE7',
                              'TABLE8',
                              'TABLE9',
                              'TABLE10')
AND column_name in ('COLUMN1','COLUMN2','COLUMN3')
0
 

Author Closing Comment

by:d27m11y
Comment Utility
Thank you for your help !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now