Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query with variable for "any letter" Expand / Collapse
Author
Message
Posted Friday, February 17, 2012 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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). 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
Posted Friday, February 17, 2012 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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
Posted Friday, February 17, 2012 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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? Thanks anyway
Post #1253967
Posted Friday, February 17, 2012 9:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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 »

Add to briefcase

Permissions Expand / Collapse