Home » SQL & PL/SQL » SQL & PL/SQL » is regulare expression the answer? insert escape characters into string
is regulare expression the answer? insert escape characters into string [message #224009] Mon, 12 March 2007 11:07 Go to next message
SaraC
Messages: 81
Registered: August 2005
Member
Hi

Heres my problem.
I have a string value and I want to check if it contains any of the values in my list of special characters e.g. <>!£()/ etc.

If the string does contain one or possibly more of the characters in the list I want to prefix it with an escape character of \
so "Hello! how are you" would be "Hello\! how are you"

I am using oracle text searching therefore the string needs to have the Oracle Text special characters escaped.

Is there a way to do this without checking for each character in my fairly long list individually and replacing individually?

I've not used regular expressions before and have tried to make sense of some examples I found but didnt have much success applying it to this situation.
Can someone point me in the right direction? Its been a long day and Im sure I am just missing something simple but just cant see how to do it.
Using oracle 10g by the way.
Thanks in advance.
Sara

[Updated on: Mon, 12 March 2007 11:28]

Report message to a moderator

Re: is regulare expression the answer? insert escape characters into string [message #224082 is a reply to message #224009] Mon, 12 March 2007 23:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I would think it would be better to remove the special characters, rather than insert escape characters. You could use translate to remove the special characters, as shown below. I doubt that adding escape characters is what you are looking for. Bear in mind that searchig for "hello/!" does not search for "hello", it searches for "hello!". If you want to search for tokens that include such special characters, then you need to specify them as printjoins and such in your lexer.

SCOTT@10gXE> CREATE TABLE your_table
  2    (your_column  VARCHAR2 (80))
  3  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO your_table VALUES ('Hello, how are you?')
  3  INTO your_table VALUES ('Have a nice day.')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@10gXE> CREATE INDEX your_index ON your_table (your_column)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@10gXE> VARIABLE string_value VARCHAR2 (60)
SCOTT@10gXE> EXEC :string_value := 'Hello! <how> (are) £you/'

PL/SQL procedure successfully completed.

SCOTT@10gXE> SELECT TRANSLATE (:string_value, '<>!£()/', ' ') FROM DUAL
  2  /

TRANSLATE(:STRING_VALUE,'<>!£()/','')
----------------------------------------------------------------------------------------------------
Hello  how are you

SCOTT@10gXE> SELECT * FROM your_table
  2  WHERE  CONTAINS (your_column, TRANSLATE (:string_value, '<>!£()/', ' ')) > 0
  3  /

YOUR_COLUMN
--------------------------------------------------------------------------------
Hello, how are you?

SCOTT@10gXE> 

[Updated on: Mon, 12 March 2007 23:41]

Report message to a moderator

Re: is regulare expression the answer? insert escape characters into string [message #224717 is a reply to message #224009] Thu, 15 March 2007 04:13 Go to previous messageGo to next message
SaraC
Messages: 81
Registered: August 2005
Member
Thanks for your reply - but I want to be able to use the special characters for searching. as in your "hello!" example.

I am trying out the printjoin/lexer suggestion as one had not been defined for my index.
So I presume trying to search with the special character token did not work because the special characters were not indexed.

Thx

[Updated on: Thu, 15 March 2007 05:06]

Report message to a moderator

Re: is regulare expression the answer? insert escape characters into string [message #225060 is a reply to message #224717] Fri, 16 March 2007 18:01 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SQL> SELECT regexp_replace('Hello! How are you?', '([?!])', '\\\1') escaped
  2  FROM dual;

ESCAPED
---------------------
Hello\! How are you\?
Re: is regulare expression the answer? insert escape characters into string [message #225250 is a reply to message #224009] Mon, 19 March 2007 05:56 Go to previous messageGo to next message
SaraC
Messages: 81
Registered: August 2005
Member
Thank you.

This works in most cases but what if I want to escape any ] characters in my string. eg 'hello [again]' I want to become 'hello \[again\]' when I put this character in my list of strings to prefix with the escape character, it is treated as the end of the list of characters as in '])' even when it is in the middle of the string. How can I get around this?

Also can you please explain what the \\\1 at the end means - I have tried to look at explanations for this but dont think the ones I have seen explain it particularly well - I know that this is the string that will add the escape character but unsure about \1.

Thanks
Re: is regulare expression the answer? insert escape characters into string [message #225323 is a reply to message #225250] Mon, 19 March 2007 12:40 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
You will not find this info in the Oracle documentation. They just assume that you can find plenty of regular expression info elsewhere, which you can, but here is a basic primer for this one particular use. If you wanted to find all instances of an e or an o followed by an l or a w and replace it with a *, you could write
SQL> SELECT regexp_replace('Hello! How [are] you?', '[eo][lw]', '*') escaped
  2  FROM dual;

ESCAPED
-------------------
H*lo! H* [are] you?
If, however, you wanted to replace it with a the letters you actually found and anything else, you need to use the parentheses-backslash combination. By wrapping the search criteria in parentheses you set them up as ordinal tokens to be used in the replacement section, in this case \1 and \2.
SQL> SELECT regexp_replace('Hello! How [are] you?', '([eo])([lw])', '*\1*\2*') escaped
  2  FROM dual;

ESCAPED
---------------------------
H*e*l*lo! H*o*w* [are] you?
The other search method is the pipe (|). You are going to need it to escape the "[" and "]". It is an OR search. In this case we search for "el" or "ow" or "you". The previous example would have found "el" or "ew" or "ol" or "ow". I am also including the \\ in this query. Since \ is the escape character in regular expressions, it is expecting a number after it to know which token to replace with. We want to print the \ though, so we just escape the \ itself, thus \\. In short \\ prints a literal \ in the replacement section.
SQL> SELECT regexp_replace('Hello! How [are] you?', '(el|ow|you)', '\\\1') escaped
  2  FROM dual;

ESCAPED
------------------------
H\ello! H\ow [are] \you?
So now you just combine both search methods -- [] and | -- to find everything. The backslash doesn't work to escape "[" or "]" inside a "[...]" so you move it outside. So you use the pipes to add the "[" and "]" to the search, and you have to escape them so the parser doesn't think you are starting a "[...]" section. Then you can add any other characters you would like to find to the "[...]" section, and voila.
SQL> SELECT regexp_replace('Hello! How [are] you?', '\[|\]|[?!]', 'X') escaped
  2  FROM dual;

ESCAPED
---------------------
HelloX How XareX youX

SQL> SELECT regexp_replace('Hello! How [are] you?', '(\[|\]|[?!])', 'X\1') escaped
  2  FROM dual;

ESCAPED
-------------------------
HelloX! How X[areX] youX?

SQL> SELECT regexp_replace('Hello! How [are] you?', '(\[|\]|[?!])', '\\\1') escaped
  2  FROM dual;

ESCAPED
-------------------------
Hello\! How \[are\] you\?
Re: is regulare expression the answer? insert escape characters into string [message #225575 is a reply to message #225323] Tue, 20 March 2007 06:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Nice break down for the OP Scott.

Just to add, you can avoid having to escape the brackets [ ] and use them inside the character list brackets directly in the following manner:
SELECT regexp_replace('Hello! How [are] you?', '([][?!])', '\\\1') escaped from dual;

@OP - the \1 is a backreference that is talked about here.
Re: is regulare expression the answer? insert escape characters into string [message #225740 is a reply to message #224009] Wed, 21 March 2007 04:02 Go to previous message
SaraC
Messages: 81
Registered: August 2005
Member
Thank you for your explanations.
Previous Topic: HOW TO CHANGE PARTICULAR STRING IN A COLUMN
Next Topic: Between !! Urgent
Goto Forum:
  


Current Time: Wed Dec 07 12:26:05 CST 2016

Total time taken to generate the page: 0.07410 seconds