SCOTT@orcl_12.1.0.2.0> VARIABLE P2_SEARCH_TERM VARCHAR2(30)
SCOTT@orcl_12.1.0.2.0> EXEC :P2_SEARCH_TERM := 'CLOUD'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT prod_description_short,
  2  -- add case statement for each score using replace and translate
  3  	    CASE
  4  	      WHEN REPLACE (TRANSLATE (:P2_SEARCH_TERM, '$#@', '   '), ' ', '') is not null
  5  	      THEN score(4)
  6  	      ELSE 0
  7  	    END sc_4
  8  FROM   ot_test
  9  where  (
 10  (
 11  (
 12  -- add replace and translate to replace special characters and spaces and check for null
 13  REPLACE (TRANSLATE (:P2_SEARCH_TERM, '$#@', '   '), ' ', '') is not null and
 14  contains ( prod_description_short,
 15  -- add translate to remove special characters but leave spaces
 16  '<query>
 17  <textquery lang="ENGLISH" grammar="CONTEXT">' || TRANSLATE (:P2_SEARCH_TERM, '$#@', '   ') || '
 18  <progression>
 19  <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
 20  <seq><rewrite>transform((TOKENS, "{", "}", " NEAR "))</rewrite></seq>
 21  </progression>
 22  </textquery>
 23  <score datatype="FLOAT" algorithm="COUNT"/>
 24  </query>',
 25  4 ) > 0
 26  )
 27  -- add replace and translate to replace special characters and spaces and check for null
 28  or REPLACE (TRANSLATE (:P2_SEARCH_TERM, '$#@', '	'), ' ', '') is null
 29  )
 30  )
 31  order by sc_4 desc
 32  /

PROD_DESCRIPTION_SHORT
--------------------------------------------------------------------------------
      SC_4
----------
Cloud computing is the on-demand availability of computer system resources, espe
cially data storage and computing power, without direct active management by the
 user.
      50.5


1 row selected.

SCOTT@orcl_12.1.0.2.0> EXEC :P2_SEARCH_TERM := NULL

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

PROD_DESCRIPTION_SHORT
--------------------------------------------------------------------------------
      SC_4
----------
A laptop computer or notebook computer, also laptop or notebook for short, is a
small, portable personal computer (PC) that is designed to be practically placed
 on the user's lap, hence the name
         0

Antivirus software (abbreviated to AV software), also known as anti-malware, is
a computer program used to prevent, detect, and remove malware. Antivirus softwa
re was originally developed to detect and remove computer viruses, hence the nam
e. However, with the proliferation of other malware, antivirus software started
to protect against other computer threats.
         0

An optical mouse is a computer mouse which uses a light source, typically a ligh
t-emitting diode, and a light detector, such as an array of photodiodes, to dete
ct movement relative to a surface.
         0

Cloud computing is the on-demand availability of computer system resources, espe
cially data storage and computing power, without direct active management by the
 user.
         0

A SaaS product is an internet software that is accessible to all users. SaaS pro
ducts are everywhere.
         0


5 rows selected.

SCOTT@orcl_12.1.0.2.0> EXEC :P2_SEARCH_TERM := 'cloud@#$computing'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> /

PROD_DESCRIPTION_SHORT
--------------------------------------------------------------------------------
      SC_4
----------
Cloud computing is the on-demand availability of computer system resources, espe
cially data storage and computing power, without direct active management by the
 user.
      50.5


1 row selected.

insert into ot_test values(6,106,'ai-chatbot','ai-chatbot,chatGPT','ChatGPT is a chatbot launched by OpenAI in November 2022. It is built on top of OpenAI''s GPT-3 family of large language models, and is fine-tuned with both supervised and reinforcement learning techniques.');

SELECT prod_description_short,
    -- add case statement for each score using replace and translate
    	    CASE
    	      WHEN REPLACE (TRANSLATE (:P2_SEARCH_TERM, '$#@', '   '), ' ', '') is not null
    	      THEN score(4)
    	      ELSE 0
    	    END sc_4
    FROM   ot_test
    where  (
   (
   (
   -- add replace and translate to replace special characters and spaces and check for null
   REPLACE (TRANSLATE (:P2_SEARCH_TERM, '$#@', '   '), ' ', '') is not null and
   contains ( prod_description_short,
   -- add translate to remove special characters but leave spaces
   '<query>
   <textquery lang="ENGLISH" grammar="CONTEXT">' || TRANSLATE (:P2_SEARCH_TERM, '$#@', '   ') || '
   <progression>
   <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
   <seq><rewrite>transform((TOKENS, "{", "}", " NEAR "))</rewrite></seq>
   </progression>
   </textquery>
   <score datatype="FLOAT" algorithm="COUNT"/>
   </query>',
   4 ) > 0
   )
   -- add replace and translate to replace special characters and spaces and check for null
   or REPLACE (TRANSLATE (:P2_SEARCH_TERM, '$#@', '	'), ' ', '') is null
   )
   )
   order by sc_4 desc
   /

SCOTT@orcl_12.1.0.2.0> VARIABLE P2_SEARCH_TERM VARCHAR2(30)
SCOTT@orcl_12.1.0.2.0> EXEC :P2_SEARCH_TERM := 'GPT-3'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT TRANSLATE (:P2_SEARCH_TERM, '!@#$%^&*()_-|\{}[]:;"<>,.?/', '   ') FROM DUAL
  2  /

TRANSLATE(:P2_SEARCH_TERM,'!@#$%^&*()_-|\{}[]:;"<>,.?/','')
--------------------------------------------------------------------------------
GPT3

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT TRANSLATE (:P2_SEARCH_TERM, '!@#$%^&*()_-|\{}[]:;"<>,.?/', '			     ') FROM DUAL
  2  /

TRANSLATE(:P2_SEARCH_TERM,'!@#$%^&*()_-|\{}[]:;"<>,.?/','')
--------------------------------------------------------------------------------
GPT 3

1 row selected.

SCOTT@orcl_12.1.0.2.0> 
SCOTT@orcl_12.1.0.2.0> SELECT prod_description_short,
  2  		 CASE
  3  		   WHEN REPLACE (TRANSLATE (:P2_SEARCH_TERM,
  4  		     '!@#$%^&*()_-|\{}[]:;"<>,.?/', '	'), ' ', '') is not null
  5  		   THEN score(4)
  6  		   ELSE 0
  7  		 END sc_4
  8  	 FROM	ot_test
  9  	 where	(
 10  	(
 11  	(
 12  	REPLACE (TRANSLATE (:P2_SEARCH_TERM, '!@#$%^&*()_-|\{}[]:;"<>,.?/', '	'), ' ', '') is not null and
 13  	contains ( prod_description_short,
 14  	-- add translate to remove special characters but leave spaces
 15  	-- if you translate 27 characters, then you need 27 spaces
 16  	'<query>
 17  	<textquery lang="ENGLISH" grammar="CONTEXT">' ||
 18  	TRANSLATE (:P2_SEARCH_TERM, '!@#$%^&*()_-|\{}[]:;"<>,.?/', '			       ') || '
 19  	<progression>
 20  	<seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
 21  	<seq><rewrite>transform((TOKENS, "{", "}", " NEAR "))</rewrite></seq>
 22  	</progression>
 23  	</textquery>
 24  	<score datatype="FLOAT" algorithm="COUNT"/>
 25  	</query>',
 26  	4 ) > 0
 27  	)
 28  	-- add replace and translate to replace special characters and spaces and check for null
 29  	or REPLACE (TRANSLATE (:P2_SEARCH_TERM, '!@#$%^&*()_-|\{}[]:;"<>,.?/', '     '), ' ', '') is null
 30  	)
 31  	)
 32  	order by sc_4 desc
 33  	/

PROD_DESCRIPTION_SHORT
--------------------------------------------------------------------------------
      SC_4
----------
ChatGPT is a chatbot launched by OpenAI in November 2022. It is built on top of
OpenAI's GPT-3 family of large language models, and is fine-tuned with both supe
rvised and reinforcement learning techniques.
      50.5


1 row selected.

SCOTT@orcl_12.1.0.2.0>