Procedure to check for spaces in a table
                                                 Last update (2008-03-04 15:14:33)
                                                                                                                   Date added (2008-02-15 12:56:05)

Summary
The are several ways to find if spaces exists in table columns data. For example to check for spaces at table test_table you can create the procedure CHK_SPACE
CREATE OR REPLACE PROCEDURE CHK_SPACE (p_table_name IN VARCHAR)
IS
v_table_name            VARCHAR(30);
v_column_name           VARCHAR(30);
v_column_name_c         CHAR(30);
v_space_count           NUMBER;
v_ret                   NUMBER; -- temp holder for above
v_initial_space_counter NUMBER;
trail_dyn_cursor             NUMBER;
lead_dyn_cursor              NUMBER;

CURSOR TABLE1 IS
 SELECT table_name
  FROM ALL_TABLES
   WHERE table_name = p_table_name;
 
CURSOR COLUMN1 IS
 SELECT column_name
  FROM ALL_TAB_COLUMNS
   WHERE table_name = v_table_name;

BEGIN
 
OPEN TABLE1;
LOOP
--  FOR crec1 in TABLE1 LOOP
    FETCH TABLE1 INTO v_table_name;

IF (TABLE1%NOTFOUND) THEN
   EXIT;
END IF;

      DBMS_OUTPUT.PUT_LINE(v_table_name);
      DBMS_OUTPUT.PUT_LINE
          ('--------------------------------------------------');
  v_initial_space_counter := 0;

OPEN COLUMN1;
LOOP
--   FOR crec2 in COLUMN1 LOOP
     FETCH COLUMN1 INTO v_column_name;

IF (COLUMN1%NOTFOUND) THEN
   EXIT;
END IF;

trail_dyn_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
  (trail_dyn_cursor,
      'SELECT COUNT(*) ' ||
      'FROM ' || p_table_name ||
      ' WHERE ' || v_column_name || ' LIKE ''% ''',
      DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN (trail_dyn_cursor, 1, v_space_count);
  v_ret := DBMS_SQL.EXECUTE(trail_dyn_cursor);
  IF DBMS_SQL.FETCH_ROWS(trail_dyn_cursor) > 0 THEN
    DBMS_SQL.COLUMN_VALUE (trail_dyn_cursor, 1, v_space_count);
  END IF;

DBMS_SQL.CLOSE_CURSOR(trail_dyn_cursor);

    IF v_space_count > 0 THEN
      v_initial_space_counter := v_initial_space_counter + 1;

    END IF;

    v_column_name_c := v_column_name;

    IF v_space_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('    trail: ' || v_column_name_c ||
TO_CHAR(v_space_count));

    END IF;

lead_dyn_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
  (lead_dyn_cursor,
      'SELECT COUNT(*) ' ||
      'FROM ' || p_table_name ||
      ' WHERE ' || v_column_name || ' LIKE '' %''',
      DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN (lead_dyn_cursor, 1, v_space_count);
  v_ret := DBMS_SQL.EXECUTE(lead_dyn_cursor);
  IF DBMS_SQL.FETCH_ROWS(lead_dyn_cursor) > 0 THEN
    DBMS_SQL.COLUMN_VALUE (lead_dyn_cursor, 1, v_space_count);
  END IF;

DBMS_SQL.CLOSE_CURSOR(lead_dyn_cursor);

    IF v_space_count > 0 THEN
      v_initial_space_counter := v_initial_space_counter + 1;
    END IF;

    v_column_name_c := v_column_name;

    IF v_space_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('    lead:  '  || v_column_name_c ||
TO_CHAR(v_space_count));

    END IF;

    END LOOP;
    CLOSE column1;

END LOOP;
CLOSE table1;

END CHK_SPACE;
/

Connect to sqlplus and run
SET serveroutput ON;
EXEC CHK_SPACE('test_table');
This will output
'test_table'
       --------------------------------------------------
       trail: Column_name                 1
       lead:  Column_name               1      
To correct possible LEADING spaces.
UPDATE table_name SET column_name = LTRIM(column_name) 
FOR example:
UPDATE test_table SET column_name = LTRIM(column_name);
To correct possible TRAILING spaces.
UPDATE table_name SET column_name= RTRIM(column_name) 
FOR example:
UPDATE test_table SET column_name = RTRIM(column_name);
Reviews
Filters
Search