Shogoot
Shogoot

Reputation: 297

Oracle SQL Search for or find character by its ascii value

My challenge is to find certain characters across multiple tables.

At my work the DB is set up to keep all date values in columns named like this d_date, d_inserteby_id, d_valid_to etc. But underscore is a reserved char in SQL right, so a straight forward like '%D_%' wont work.

So when im looking for rows containing a reference to a date i have to be a bit more creative then '%D_%'

I know i can: select * from table t where Upper(T.column) like '%D_%' escape '\'

But how to search for that d and underscore using its ascii value?

Upvotes: 1

Views: 17791

Answers (3)

TJ Abrahamsen
TJ Abrahamsen

Reputation: 51

@Shogoot: If I understand you correctly, you want to be able to search through any tables that have any ROW of any column that has a VALUE of your search string.

If the above is a correct assumption, I think your only solution is to use a block of PL/SQL, like shown here:

DECLARE

    -- Find tables that have the right type of columns
    CURSOR cur IS
        SELECT  atc.owner
                ,atc.table_name
                ,atc.column_name
        FROM    all_tab_columns atc
        WHERE   atc.data_type IN ('VARCHAR2', 'CHAR', 'DATE')
        AND     owner IN ('OE')
        ;

    vTestString VARCHAR2(250) := CHR(45); -- CHR(45) = '-'      
    iCount      INTEGER := 0;
    vStatement  VARCHAR2(2000) := '';
BEGIN

    -- Loop through tables and columns 
    FOR rec IN cur LOOP
        BEGIN
            -- Find tables that have the right type of columns
            vStatement  :=  'SELECT COUNT(*) '
                            || 'FROM ' || rec.owner || '.' || rec.table_name || ' '
                            || 'WHERE ' || rec.column_name || ' LIKE ''%' || vTestString || '%'' '
                            ;

            --dbms_output.put_line(vStatement);
            EXECUTE IMMEDIATE vStatement
            INTO iCount
            ;

            IF (iCount > 0) THEN
                dbms_output.put_line('Found ' || iCount || ' matches in: ' || rec.owner || '.' || rec.table_name || '.' ||  rec.column_name);
            END IF;     
        EXCEPTION
            -- Oops, we might end here if there is an error
            WHEN OTHERS THEN
                dbms_output.put_line('Something messed up with: ' || rec.owner || '.' || rec.table_name || '.' ||  rec.column_name || '(' || SQLERRM || ')');
        END;
    END LOOP;
END;

You will have to change the search string to whatever you need. The query above is based on Oracle's OE schema, and you will have to tweak the "cur" query to fit your needs.

Also, keep in mind that you might search through a LOT of data, so use it based on your own comfort.

Hope this helps.

~ TJ

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52346

You can't search for a column name in the table that it's part of, you have to look at the data dictionary and use dynamic SQL if your schema changes frequently.

You can ...

select table_name,
       column_name
from   all_tab_columns
where  owner = ? -- insert name of table owner
and    upper(column_name) like 'D\_%' escape '\'

There's no advantage to using the ASCII value of D in the search -- it would just obfuscate the code. LIKE and ESCAPE is the correct approach.

Using mixed-case object names is considered a bad practice in Oracle, by the way.

Edit: of course if you really wanted to search for strings by their ASCII characters then you'd do something like:

where ascii(substr('D_123',1,1))=68 and
      ascii(substr('D_123',2,1))=95

or

where ascii(substr('D_123',1,1))||ascii(substr('D_123',2,1))='6895'

or

where substr(dump(substr('D_123',1,2)),-6) = ' 68,95'

As ever, lots of ways to do things the wrong way.

Upvotes: 3

Frank Schmitt
Frank Schmitt

Reputation: 30775

I'm still not sure why you want to use the ASCII value instead of the literal underscore, but perhaps this is what you want (95 is the ASCII code for '_'):

  select * 
  from t 
  where upper(t.column1) like '%D\' || chr(95) || '%' escape '\'

Please note that this will raise an error if you try to replace the 95 with e.g. 65 (for an 'A'), since you cannot escape normal characters.

To handle this, you'd need some kind of logic to distinguish between normal characters and characters you have to escape.

Upvotes: 0

Related Questions