sql - Check if a string contains a value from one table -

sql - Check if a string contains a value from one table -

so, have table t_type, contains values :

t_type :

id | lib_type -------------- 1 | highschool 2 | university 3 | unknown

i create select query table, give me total name (for illustration : foch highschool, university of paris, random enterprise).

then, want select , in t_type, row fetch first seelct :

select * t_type lib_typ contains '%'||fullname||'%';

so, f got foch highschool :

it must homecoming 1 | highschool

if university of paris :

it must homecoming 2 | university

and, if select in t_type not bring lines,

it homecoming 3 | unkown

i found samples, show how search, :

select * t_type contains (lib_type, fullname ,1)

it not work, because fullname greater lib_type.

what want is, if part of fullname, contains lib_type table, homecoming id , lib_type of line.

is possible?

i hope i'm clear, if not, tell me.

as far can see, you're looking this;

select id, lib_type ( select id, lib_type, 1 prio mytable upper('foch highschool') upper('%'||lib_type||'%') union select id, lib_type, 2 prio mytable id=3 order prio ) z rownum=1;

an sqlfiddle test with.

it selects first lib type matching input, , if none matches gets row id 3.

it setting prio 1 on hits, , prio 2 on default, sorting prio , getting first match.

sql oracle