Using 'CharIndex()' within an Advanced Query returns an error...

Using 'CharIndex()' within an Advanced Query returns an error...

  
I'm trying to query my database for all rows in a table that are not prefixed with a "(" character. I'm using the CharIndex() function to filter out any names with "(" as their first character. When I test the query, I'm being told that CharIndex() is invalid? Any suggestions?

(I am currently referencing an Oracle server, could this be the problem?)
SELECT 
    {OrganizationUnit}.*,
    {User}.*,
    CASE WHEN Dep.[Id] IS NULL THEN {OrganizationUnit}.[Name] ELSE Dep.[Name] END AS Department
FROM {OrganizationUnit} 
LEFT JOIN {User} ON {OrganizationUnit}.[ManagerId] = {User}.[Id]
LEFT JOIN {OrganizationUnit} Dep ON {OrganizationUnit}.[ParentOrgUnitId] = Dep.[Id] 
WHERE CharIndex('(',{OrganizationUnit}.[Name]) <> 1
ORDER BY Department,{OrganizationUnit}.[Level],{OrganizationUnit}.[Name] 

Hi Daryl,

Try to use instr function. See here the signature.

Hope that helps
Hi.

When writing an Oracle query, you must use the Oracle functions.
Instr is more complete than CharIndex, it allows you to search "starting at" and "nth occorrence".
Thanks for the confirmation! As you probably guessed, I'm a SQL-guy working in a Oracle environment. I was sure it was a matter of using the INSTR() instead. I'm new to OutSystems and I just wanted to be sure this type of incompatibility should be expected.

Thanks again!
As an alternative, I'd use a NOT LIKE, like this:

WHERE {OrganizationUnit}.[Name] NOT LIKE '(%'

This saves you the CharIndex/INSTR (so better compatability across platforms), and NOT LIKE being standard SQL, the query optimizing might be able to produce better results, e.g. by using an index (I can imagine the CharIndex/INSTR approach needs a full table scan, as the database engine/optimizer does not know the semantics of the function)
some Sql optimization tips:

http://hungred.com/useful-information/ways-optimize-sql-queries/


Kilian Hekhuis wrote:
As an alternative, I'd use a NOT LIKE, like this:

WHERE {OrganizationUnit}.[Name] NOT LIKE '(%'

This saves you the CharIndex/INSTR (so better compatability across platforms), and NOT LIKE being standard SQL, the query optimizing might be able to produce better results, e.g. by using an index (I can imagine the CharIndex/INSTR approach needs a full table scan, as the database engine/optimizer does not know the semantics of the function)
 Nice!! I'm lovin' the 'cross-platform' compatibility. That's so simple, I don't know why it didn't occur to me? I guess because I'm out of my SQL comfort-zone, I'm automatically expecting it to be difficult. 

Thanks guys!
 
Hi Daryl and Kilian,

I agree with Kilian the like is more platform-independent. 

But I'm not sure about the best solution. In my opinion you can use:
    WHERE substr({OrganizationUnit}.[Name],1,1) != '('
where the substr(string, pos, length) function gets [length] characters from [string] starting at [pos].
The reason is because you can create a function based index "substr(Name,1,1)" on the Name column.
It is not possible to create an index with a like construction.

Perhaps more people can tell their experience about this point. Or perhaps it's written somewhere on the internet. 
Regards,

Hans
@Hans: I'm not too much of an SQL guru to make any in-depth comment on what you describe. It might be that SQLserver is smart enough to equate the WHERE substr with the index. Rules on Oracle may differ. However, using LIKE patterns is cross-platform compatible, and in general, I think that they have a greater chance of being optimized by a given database platform than when using functions.
A good article on The SQL LIKE operator and the usage of indexes 

Tip

Avoid LIKE expressions with leading wildcards (e.g., '%TERM').

http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning