How to deal with oracle character query in Wildcard

[Shanbaag]

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.

[Ackley]

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.

[pancham]

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.

[Mecurtis]

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.