Skip to Content (Press Enter)
2021-01-04 17-46-03
Daryl Van Johnson
112Views
9Comments
Using 'CharIndex()' within an Advanced Query returns an error...
Question
SQL
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]

0
0
26 Mar 2014
Copy Link
2016-04-21 18-13-58
Nuno Rolo
 
MVP
Hi Daryl,

Try to use instr function. See here the signature.

Hope that helps
1
0
26 Mar 2014
Copy Link
2016-04-22 00-29-45
Nuno Reis
 
MVP
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".
1
0
26 Mar 2014
Copy Link
2021-01-04 17-46-03
Daryl Van Johnson
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!
0
0
26 Mar 2014
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
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)
0
0
02 Apr 2014
Copy Link
2021-01-04 17-46-03
Daryl Van Johnson
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!
 
0
0
02 Apr 2014
Copy Link
2024-12-17 14-32-59
Matthias Preuter
 
MVP
some Sql optimization tips:

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


0
0
02 Apr 2014
Copy Link
2021-07-07 13-36-32
Hans Dollen
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
0
0
03 Apr 2014
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
@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.
0
0
03 Apr 2014
Copy Link
2024-12-17 14-32-59
Matthias Preuter
 
MVP
A good article on The SQL LIKE operator and the usage of indexes 

Tip

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

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning
0
0
03 Apr 2014
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.