CREATE OR ALTER PROCEDURE SP_SPLIT_INTO_WORDS (A_TEXT VARCHAR(32000), A_DELS VARCHAR(100) default ',.<>/?;:''"[]{}`~!@#$%^&*()-_=+\|/', A_SPECIAL CHAR(1) default ' ') RETURNS ( WORD VARCHAR(50), POSIC integer ) AS DECLARE norder integer; BEGIN -- Aux SP, used only in oltp_data_filling.sql to filling table PATTERNS -- with miscelan combinations of words to be used in SIMILAR TO testing. norder = 0; for with recursive j as( -- loop #1: transform list of delimeters to rows select s,1 i, substring(s from 1 for 1) del from( select replace(:a_dels,:a_special,'') s from rdb$database ) UNION ALL select s, i+1, substring(s from i+1 for 1) from j where substring(s from i+1 for 1)<>'' ) ,d as( select :a_text s, :a_special sp from rdb$database ) ,e as( -- loop #2: perform replacing each delimeter to `space` select d.s, replace(d.s, j.del, :a_special) s1, j.i, j.del from d join j on j.i=1 UNION ALL select e.s, replace(e.s1, j.del, :a_special) s1, j.i, j.del from e -- nb: here 'column unknown: e.i' error will be on old builds of 2.5, -- e.g: WI-V2.5.2.26540 (letter from Alexey Kovyazin, 24.08.2014 14:34) join j on j.i = e.i + 1 ) ,f as( select s1 from e order by i desc rows 1 ) ,r as ( -- loop #3: perform split text into single words select iif(t.k>0, substring(t.s from t.k+1 ), t.s) s, iif(t.k>0,position( del, substring(t.s from t.k+1 )),-1) k, t.i, t.del, iif(t.k>0,left(t.s, t.k-1),t.s) word from( select f.s1 s, d.sp del, position(d.sp, s1) k, 0 i from f cross join d )t UNION ALL select iif(r.k>0, substring(r.s from r.k+1 ), r.s) s, iif(r.k>0,position(r.del, substring(r.s from r.k+1 )),-1) k, r.i+1, r.del, iif(r.k>0,left(r.s, r.k-1),r.s) word from r where r.k>=0 ) select word from r where word>'' INTO word do BEGIN posic = norder; suspend; norder = norder + 1; end end; CREATE TABLE alumno_srch_nombre( id integer NOT NULL, valor varchar(255) NOT NULL, orden integer ); CREATE INDEX ndx_alum_srch_nombre ON alumno_srch_nombre(id, valor); CREATE OR ALTER TRIGGER t_alumno_biu_ndx FOR alumno BEFORE INSERT OR UPDATE AS BEGIN DELETE FROM alumno_srch_nombre WHERE id = NEW.id; INSERT INTO alumno_srch_nombre SELECT NEW.id, word, posic FROM SP_SPLIT_INTO_WORDS(NEW.apenom); END; SELECT * FROM ( SELECT a.*, CASE WHEN a.apenom LIKE replace(:INPUT,' ','%') || '%' THEN 0 ELSE 1 END NIVEL FROM alumno a INNER JOIN ( SELECT id, count(*) cnt FROM ( SELECT id, word, min(orden) orden FROM SP_SPLIT_INTO_WORDS(:input) x INNER JOIN ALUMNO_SRCH_NOMBRE asn ON asn.valor LIKE word || '%' GROUP BY id, word ) GROUP BY id HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input)) ) b ON b.id=a.id ) ORDER BY NIVEL; --- OTHER EXAMPLE: SELECT a.* FROM alumno a INNER JOIN ( SELECT id, count(*) cant FROM alumno INNER JOIN SP_SPLIT_INTO_WORDS(:input) X ON ALUMNO.APELLIDOS LIKE X.WORD||'%' OR ALUMNO.NOMBRES LIKE X.WORD||'%' OR ALUMNO.CEDULA LIKE X.WORD||'%' GROUP BY id HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input)) ) x ON a.id = x.id UNION SELECT a.* FROM ALUMNO a where a.cedula LIKE :input || '%'
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter