SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query with variable for "any letter"


Query with variable for "any letter"

Author
Message
batistuta789
batistuta789
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 4
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28123 Visits: 39939
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

batistuta789
batistuta789
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 4
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28123 Visits: 39939
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search