Hi,
I am trying to highlight a search term in a result set feeding a web page.
So far I can highlight the entire result, but not just the term match.
The problem is in white space mismatches between the column data and the search term that is entered.
(The web server drivers apperently don't support case or with.)
I feel there is probably a way to do this with regular expressions, but I can't figure out how.
Simple example:
SQL> select rec,
2 decode(nvl(regexp_count(replace(rec, ' ', ''), search_term, 1, 'x'), 0),
3 0, rec,
4 '<span>'||rec||'</span>') result
5 from (select replace('NW 26', ' ', '') search_term from dual),
6 (select '2704 NW 26TH STREET' rec from dual
7 union all
8 select '2704 NW26TH STREET' rec from dual
9 union all
10 select '2704 NW 27TH STREET' rec from dual
11 ) info
12 where replace(rec, ' ', '') like '%'||search_term||'%'
13 /
REC RESULT
------------------- --------------------------------
2704 NW 26TH STREET <span>2704 NW 26TH STREET</span>
2704 NW26TH STREET <span>2704 NW26TH STREET</span>
My prefered result
REC PREFERED RESULT
------------------- --------------------------------
2704 NW 26TH STREET 2704 <span>NW 26</span>TH STREET
2704 NW26TH STREET 2704 <span>NW26</span>TH STREET
tia
Maybe?
You can ignore whitespace using the final parameter of regexp_replace. Set this to 'x' to do this.
But this only ignores whitespace in your pattern. So if you get "NW 26", "NW26" matches in your source. But not "NW 26":
select rec,
search_term,
regexp_replace (
rec , '(' || search_term || ')', '<span>\1</span>', 1, 1, 'x'
) res
from (
select 'NW 26' search_term from dual
), (
select '2704 NW 26TH STREET' rec from dual
union all
select '2704 NW26TH STREET' rec from dual
union all
select '2704 NW 27TH STREET' rec from dual
) info;
REC SEARCH_TERM RES
2704 NW 26TH STREET NW 26 2704 NW 26TH STREET
2704 NW26TH STREET NW 26 2704 <span>NW26</span>TH STREET
2704 NW 27TH STREET NW 26 2704 NW 27TH STREETSo if you can find a safe way to remove unwanted spaces in your data, you could be OK here.