Character scape "\" doesn´t work in a query

i need to make the tipical query:

select all r_object_id
from dm_folder where folder ('/Cabinet/Consultoría',descend)

The problem is that some folders has names with special characters - for example a folder called "Consultoría", that has an accent in the letter "i" as you can see. I have readen that it exists the scape character "\"but is doesn´t work in this kind of querys. I don´t mind to use the Character "_" that means "one and only one character" to put in any special character - I know this wouldn´t be an ellegant and perfect solution, but it would be a good enough solution for what I need to use.

Somebody knows how can I solve this problem?.

Thanks

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

dm_user

If it worked, then make the thread as correct.

All comments

dm_user

The ESCAPE Character

There may be occasions when the pattern you want to match includes a percent sign (%)

or an underbar (_). To match either of those characters literally, you must specify an

escape character and insert that character before the percent sign or underscore in the

pattern. Use the optional ESCAPE clause to specify the escape character.

For example, suppose you want to find all documents whose object names contain an

underscore. Because the underscore is interpreted as a wild card by default, you must

define and use an escape character in the query:

SELECT "r_object_id" FROM "dm_document"

WHERE "object_name" LIKE ’%\_%’ ESCAPE ’\’

In the above example, the backslash is defined as the escape character. Placed in the

pattern directly ahead of the underscore, it tells the server to treat the underscore as a

literal character rather than a wild card.

In addition to the wild-card characters, an escape character can also escape itself. For

example, suppose you want to match the string %_\ and that you have defined the

backslash as the escape character.Your LIKE predicate would look like this:

LIKE ’\%\_\\’ ESCAPE ’\’

In the above example, the backslash character escapes not only the percent sign and

underscore but also itself.

Escape characters can escape only the two wild-card characters and themselves. If you

insert an escape character before any other character in a pattern, it generates an error.

You can use any printable character as an escape character.

Regards,

Arindam

dm_user

Precisely, if you have character like _ then use LIKE '%\_'ESCAPE'\'

for e.g.

object_name is abc_bcx_ then query will be

select * from dm_document where object_name like 'abc%\_'ESCAPE'\';

I hope it will help you.

~Arindam

danonino

ok. If I do this query:

select *

from dm_folder

where object_name like 'Consultor\ía'

I have tryed the scape character just before the letter "i" because it has an accent. It doens´t work. Somebody knows why?

dm_user

Hi,

Sorry for delayed reply.

Here is the query:

select * from dm_document where object_name like 'Consultor\\%'ESCAPE'\';

Explanation:

\ is the escape character. so, \\ is mentioned and then % is for matching sequence for those words which will start with 'Consultor\' . and then at the end 'ESCAPE'\' you have to put.

I think now it is clear to you.

~Arindam

dm_user

If it worked, then make the thread as correct.

Nishant

Hi Danonino,

The use of escape character is perfectly alright to use in queries....

If you want to escape the secial character of 'i' use query

select * from dm_folder where object_name like 'Consultor_a'

select * from dm_folder where object_name like 'Consultor%a'

Remember the % sign will escape multiple characters, and _ will escape single character.

Sorry changed query: Didn't see you have changed query.

Message was edited by: Nishant

lakgs

An add-on question...

The naming convention that we currently use for our documents is Grp-Type-012345. As noted in other posts, this hyphen/special character is problematic in object_name.

I have been testing using the underscore (Grp_Type_012345) and have been experiencing the same problems in advanced search.

Should I be escape characters in the advanced search as well in DQL statements?

Thanks.