February 17, 2012 at 8:14 am
Hello,
I am actually setting up a wizard function on an oracle database (this wasn´t part of the job description:-)). But I am just working with a kind of Wizard interface, so I am not coding anything.
The wizard will filter out specific keywords of database content and applies specific rules to this content. Anyway, many words in the content contain special letters, and the wizard rules won´t consider these words, which has a very negative impact on the Wizard quality.
I got the advice to replace special letters in the query with a variable that stands for any character. Example (the keyword should be "coopération"):
(coop[!e]ration) -> where [!e] would stand for any letter expect e.
The rule itself does not work, but I am asking myself (and now you) if such `character variables´ could be an option and how they work. In an ideal case, the variable would even replace a character row, because the special characters are possibly coded into several characters.
Thanks,
Peter
February 17, 2012 at 8:30 am
both SQL and Oracle support pattern matching for any character:
select * from YourTable
where YourColumn like '%H_M%'
SQL supports a type of regular expression like this:
select * from YourTable
where YourColumn like '%H[a,e,i,o,u,A]M%'
select * from YourTable
where YourColumn like '%H[^e]M%' --<your example NOT E, but any other letter:
where Oracle requires you to use the regular expression object to do the same:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions007.htm
does that help?
Lowell
February 17, 2012 at 9:07 am
Hey Lowell,
that´s a good page, though it didn´t help me yet. Isn´t there a way simple way to follow the below example from the page in a way that keeps the `in between´ content of a word open to anything:
"The following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name begins with Ste and ends with en and in between is either v or ph):
SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');
"
In my example, it could be something like
'coop(%)ration'
and filter out all words that start with coop and end with ration. This would avoid the nasty special character problem. Unfortunately, it won´t work.
Or did I possibly not fully understand your reply?:-) Thanks anyway
February 17, 2012 at 9:18 am
it goes back to what you said you want to do...I might be misunderstanding.
I thought you said you said both find anything and find anything except...
will something like this work for you?
SELECT *
FROM YOURTABLE
WHERE YOURCOLUMN LIKE '%coop_ration%'
AND NOT LIKE '%cooperation%'
SELECT *
FROM YOURTABLE
WHERE YOURCOLUMN LIKE '%coop%ration%' --allows multipe letters between
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply