Search for String Across Columns : VARCHAR2 « SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL Data Types
3. VARCHAR2

SQL>
SQL> CREATE TABLE myRoom
  2  (name   VARCHAR(10)
  3  ,floorcolor VARCHAR(10)
  4  ,ceilingcolor VARCHAR(10)
  5  ,wallcolor VARCHAR(10)
  6  );

Table created.

SQL> INSERT INTO myRoom VALUES ('Jim','RED','GREEN','YELLOW');

1 row created.

SQL> INSERT INTO myRoom VALUES ('Bob','YELLOW','BLUE','BLACK');

1 row created.

SQL> INSERT INTO myRoom VALUES ('Allan','BLUE','PINK','BLACK');

1 row created.

SQL> INSERT INTO myRoom VALUES ('George','BLUE','GREEN','OAK');

1 row created.

SQL>
SQL> SELECT name FROM myRoom
  2  WHERE floorcolor||ceilingcolor||wallcolor like '%YELLOW%';

NAME
----------
Jim
Bob

SQL>
SQL> DROP TABLE myRoom;

Table dropped.

SQL>