[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Scenario: Column with data including the Space - ASCII character

Posted on 2014-01-21
15
Medium Priority
?
480 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 74

Expert Comment

by:sdstuber
ID: 39798633
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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39798638
these conditions would also work

substr(a,-1) = ' '

regexp_like(a,' $')

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

LIKE '% '
0
 
LVL 13

Expert Comment

by:magarity
ID: 39798659
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 39798666
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 74

Expert Comment

by:sdstuber
ID: 39798670
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
ID: 39798684
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 74

Expert Comment

by:sdstuber
ID: 39798692
Done,  see above: http:#a39798666  it doesn't even use RTRIM  (but it could)
0
 

Author Comment

by:d27m11y
ID: 39798695
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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39798703
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
ID: 39798773
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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39798778
>>> 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
ID: 39798783
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 74

Expert Comment

by:sdstuber
ID: 39798788
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 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39798792
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
ID: 39798816
Thank you for your help !
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 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