Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 11-08-2009
Member
 
Join Date: Apr 2009
Posts: 107
How to deal with oracle character query in Wildcard

How do I make a SELECT statements where I want results from everything that starts with certain letter? let suppose in the excel file there are list of member name and if i wanted to find out the letter starting with letter "D" Is there a way to limit the query expansion to only, say 100, results that get returned from the index? Please help.
Reply With Quote
  #2  
Old 11-08-2009
Member
 
Join Date: Dec 2008
Posts: 183
Re: How to deal with oracle character query in Wildcard

Developers generally tend to use characters such as % as a delimiter for storing data in an Oracle database. Here you can also use the same select statement,

SELECT .....
FROM ...
WHERE NAME LIKE 'A%'

The "%" is a wildcard symbol. So in the above query, I am looking anyone whose name begins with "D". I can also use

WHERE NAME LIKE '%Rajesh%';

The above will look for names with RAJESH within its string.

WHERE NAME LIKE 'RAJEEV%'

will look for names with RAJE as the first four characters.
Reply With Quote
  #3  
Old 11-08-2009
Member
 
Join Date: Dec 2008
Posts: 202
Re: How to deal with oracle character query in Wildcard

Oracle views the % character as a Wildcard character, it can't use it as a delimiter effectively.

SELECT *
FROM wildcard
WHERE test LIKE '23_5';

Fortunately, Oracle provides a way out for such a problem?the ESCAPE character. To avoid the error, you can increase your WILDCARD_MAXTERMS. If that is not sufficient, then you have to use something more restrictive. The ESCAPE keyword comes handy when you have a character in your data which is same as a Wildcard character.
Reply With Quote
  #4  
Old 11-08-2009
Member
 
Join Date: Apr 2008
Posts: 193
Re: How to deal with oracle character query in Wildcard

The wildcard_maxterms is the number of distinct words that begin with auto, such as automotive, automobile, automatic, and so on. If there are more such distinct words in your index, then you get the error. Your query will look like this:
SELECT ObjectID FROM ObjectTable WHERE ObjectID LIKE '1\%%' ESCAPE '\'

Here the keyword ESCAPE and the character in the quotes (\) specifies that there is a escape character and the character is specified in the quotes.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to deal with oracle character query in Wildcard"
Thread Thread Starter Forum Replies Last Post
Query in SQL v/s Query in Oracle Satchel Software Development 4 29-12-2010 07:19 AM
Query for columns in oracle database Gerri Windows Software 4 10-02-2010 08:27 PM
What are the different Wildcard characters in SQL? Leana_rus Software Development 3 25-11-2009 10:58 AM
How to Setup Wildcard DNS ReD-GuY-ReD-HaT Operating Systems 3 01-08-2009 06:39 PM


All times are GMT +5.5. The time now is 01:04 AM.