Reputation: 297
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
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
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
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