Query with variable for "any letter"

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Query with variable for "any letter"

Query with variable for "any letter"

Rate Topic

Display Mode

Topic Options

Author

Message

batistuta789

Posted Friday, February 17, 2012 8:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 17, 2012 9:06 AM
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 Smile

). 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

Post #1253909

Lowell

Posted Friday, February 17, 2012 8:30 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1253931

batistuta789

Posted Friday, February 17, 2012 9:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 17, 2012 9:06 AM
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? Smile

Thanks anyway

Post #1253967

Lowell

Posted Friday, February 17, 2012 9:18 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1253977

« Prev Topic | Next Topic »

Permissions