views:

113

answers:

7

How to select all records,that may contain specific value that is known, without referring to specific column in SQL expression?

For instance, i know,that some unknown column holds value 'xxx' and there are many columns and records in table.

Thank you.

+2  A: 
SELECT * FROM table WHERE column='xxx';

But if you have many columns which can contain this value, you need to use OR:

SELECT * FROM table WHERE column1='xxx' or column2='xxx' or column3='xxx';
Māris Kiseļovs
tried, got:ORA-00936: missing expression00936. 00000 - "missing expression"
sergionni
i mentioned select query without referring on specific column,is it possible in SQL.Perhaps,some specific keyword exists.
sergionni
the question is not to specify the column
sagar
+2  A: 

If you cannot explicitly write all the possible columns, you should generate a dynamic SQL query using the schema metadata.

Marco Mariani
A: 

select * from table_name where(Table_Attribute='XXX');

this will show you all records with attribute XXX

sagar
sagar, what do you mean as TABLE_ATTRIBUTE?
sergionni
thats the name of your column like student_Id
sagar
or like Student_Name='XXX'
sagar
@sagar: this is what question is asking, column name is unknown
Shantanu Gupta
but where clause should have a condition
sagar
@sagar: this is what is needed, this question can be solved using object id and information schema only as per my knowledge
Shantanu Gupta
A: 

Run this to get desired result, sry for bad naming used.

declare @_var nvarchar(1000)
declare @var1 nvarchar(1000)
declare @var2 nvarchar(1000)
declare _cur cursor 
for select 

case Column_name 
            when '' then '' 
            else Column_name+'=''asd'' OR ' 
        end 
from information_schema.columns 
    where table_name='M_Patient' 
            and 
          data_type ='nvarchar'

open _cur
fetch _cur into @_var
while(@@fetch_status=0)
begin
set @var2=isnull(@var2,'')[email protected]_var
fetch _cur into @_var

end
close _cur
deallocate _cur

set @var1='select * from M_Patient where '+ substring(@var2, 0,len(@var2)-2)

execute (@var1)
Shantanu Gupta
what is Column_name?
sergionni
@sergionni: it is a meta data stored in sql server. write a query in sql server query analyzer as- **select * from information_schema.columns** and you will start getting what you need to do to query your result
Shantanu Gupta
got error:ORA-00942: table or view does not exist
sergionni
@sergionni: which query did u run, can u show the query u executed
Shantanu Gupta
select Column_name from information_schema.columns where table_name='STORE' and data_type ='varchar2';
sergionni
@sergionni: ='varchar2' please check 2 is typed along with you condition. try to check whether you are getting any result or not using most simple query. **select * from information_schema.columns**
Shantanu Gupta
@sergionni - the reason this query does not work for you is that it doesn't use the Oracle data dictionary or indeed Oracle syntax. This might work in SQL Server though.
APC
@APC: yes this query will work on sql server and will not work on Oracle as information_schema is a set of views provided in sql server to keep metadata about tables. There would be something similar in oracle i suppose
Shantanu Gupta
yes, there is and I provided a link in my answer. see answer by APC as well
Marco Mariani
+8  A: 

So, you want to do a Google-like free text search over your database. This can be done but the performance will be Teh Suck! Google is fast because it has indexes on its indexes, duplicate data stores and generally optimizes everything for precisely this kind of search.

Anyway, here is a proof of concept using dynamic SQL and the Oracle data dictionary. Note that I restrict the columns to the type of data I want to search for i.e. strings.

SQL> set serveroutput on size unlimited
SQL> declare
  2      dummy varchar2(1);
  3  begin
  4      for r in ( select table_name, column_name from user_tab_cols
  5                 where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )
  6      loop
  7          begin
  8              execute immediate 'select null from '||r.table_name
  9                      ||' where '||r.column_name||' like ''%&search_value%'' '
 10                      ||' and rownum = 1'
 11                 into dummy;
 12              dbms_output.put_line('Found it in >>>'
 13                     ||r.table_name||'.'||r.column_name);
 14          exception
 15              when others then
 16                  -- bad practice ahoy!
 17                  null;
 18          end;
 19      end loop;
 20  end;
 21  /
Enter value for search_value: MAISIE
old   9:                ||' where '||r.column_name||' like ''%&search_value%'' '
new   9:                ||' where '||r.column_name||' like ''%MAISIE%'' '
Found it in >>>T23.NAME

PL/SQL procedure successfully completed.

SQL>

A more robust implementation might need to handle case, whole words, etc. If you're on 10g or higher then regular expressions could be useful, but combining regex and dynamic SQL is an, er, interesting prospect.

I repeat that performance is going to be Teh Suck! on a large data set. It is virtually impossible to tune, because we cannot index every column, and certainly not to support LIKE or similar fuzzy matches. An alternative approach would be to use use XQuery to generate an XML representation of your data and then use Text to index it. Maintaining such a repository would be overhead, but the effort would be a sound investment if you need this functionality of a regular basis, especially in a production environment.

APC
A: 

I usually use this script if I need to search for a value in a database and I don't know the table and/or column. Just set the @SearcStr parameter and push play. Maybe it can help you along.

DROP TABLE #Results

DECLARE @SearchStr nvarchar(100)
SET     @SearchStr = ''

CREATE TABLE 
    #Results(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON
DECLARE @TableName nvarchar(256)
DECLARE @ColumnName nvarchar(128)
DECLARE @SearchStr2 nvarchar(110)

SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT 
            MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    
            INFORMATION_SCHEMA.TABLES
        WHERE
                TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT 
                MIN(QUOTENAME(COLUMN_NAME))
            FROM    
                INFORMATION_SCHEMA.COLUMNS
            WHERE           
                    TABLE_SCHEMA = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT 
    ColumnName
   ,ColumnValue 
FROM 
    #Results
ramad
The question is flagged [Oracle] and unfortunately your query doesn't use the Oracle data dictionary or Oracle syntax.
APC
I seem to have lost my eyes - sry about that :)
ramad
+1  A: 

If you need to do this once or twice then APC's answer is good. If this is somehow (shudder) part of an ongoing requirement, then I think the best you'll be able to do is to create an Oracle computed field on the table or tables of interest and search on that. Use a delimiter that you're sure won't show up in the actual text values, e.g.:

alter table mytable add search_column 
 as (mycolumn1||'^'||mycolumn2||'^'||mycolumn3);

Now your query becomes something like:

select <whatever transformation you want to see here> 
from mytable where search_column like '%^xxx^%'

(That sound you may have just heard was Codd spinning in his grave)

dpbradley
This is a clever solution; I wish I'd thought of it. (You will, Oscar, you will). There are a couple of problems. Firstly, virtual columns were introduced in 11g so it is not a viable solution for anybody on an earlier database. The more serious problem is that virtual columns are subject to the 4000 character limit for VARCHAR2 columns. Any longer and it hurls `ORA-54004` (who knew OERR numbers went that high?). Currently virtual columns cannot be clobs.
APC
@APC - you're right - I completely overlooked the varchar2 limitation which reduces usefulness for wide tables. Thanks for the pointer to 54004 - good to know.
dpbradley