Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Scenario: Column with data including the Space - ASCII character

Posted on 2014-01-21
15
Medium Priority
?
478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

618 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