SQL's CHARINDEX Syntax exception

Question

I am trying to select numeric part of string and find a maximum from it. I write this code, but something goes wrong, when i added CHARINDEX function.

  Integer sss= (Integer) em.createQuery("SELECT max(cast(SUBSTRING(s.fam, 1, CHARINDEX('-',s.fam)-1) as integer)) FROM Teacher s").getSingleResult();

It gives me:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Caused by: org.postgresql.util.PSQLException: ERROR: Syntax error (approximately position: "as")

I got the same with PATINDEX and LOCATE functions.

`SELECT max(cast(SUBSTRING(s.fam, 1, CHARINDEX('-',s.fam)-1))) as integer FROM Teacher s` — Youcef LAIDANI, Jan 17 '20 at 19:44
https://stackoverflow.com/questions/30778444/what-is-the-charindex-sql-server-equivalent-in-postgresql/30780989 — PM 77-1, Jan 17 '20 at 19:45
Your error points to PostgreSQL - does it have the function you are using? — PM 77-1, Jan 17 '20 at 19:47
"SQL" has no function `charindex()` and neither does PostgreSQL. All supported functions are listed here: https://www.postgresql.org/docs/current/functions-string.html — , Jan 17 '20 at 19:57

score 1 · Answer 1 · answered Jan 17 '20 at 20:53

1

Well STRPOS works just fine in Postgres 12 (fiddle): https://dbfiddle.uk/?rdbms=postgres_12&fiddle=23ab8f41f29775f056717fc6af8c2b71

You need to replace charindex() with strpos()

All this is in the comments, I know, but maybe you had a problem with the fact that STRPOS parameters are like this:

strpos(string, substring)

STRPOS DEMO

And for CHARINDEX are like this:

CHARINDEX(substring, string)

CHARINDEX DEMO

In your line of code you also have to be careful with :

ERROR: negative substring length not allowed This will hapend when there is no '-' in the string

Here is example for that.

ERROR: invalid input syntax for type integer: "test"

Here is example for that

answered Jan 17 '20 at 20:53

VBoka

8,995
3
16
24

Thanks for your answer, but with this – Vytsalo Jan 18 '20 at 06:53
Integer sss= (Integer) em.createQuery("SELECT max(cast(SUBSTRING(s.fam, 1, strpos(s.fam, '-')-1) as integer)) FROM Teacher s").getSingleResult(); i got the same error – Vytsalo Jan 18 '20 at 06:53
@Vytsalo have you checked the possible problems I have warned about ? HAve you checked the data in s.fam column ? – VBoka Jan 18 '20 at 09:02
yes, it have only 2 records with fields "123-aks" and "348-cbs" – Vytsalo Jan 18 '20 at 09:27
Ok, so, two questions : 1. with my code and this data you get the same error like in your question ? 2. Is this the text for the error in your question the whole text ? Cheers! – VBoka Jan 18 '20 at 10:08
Hi @Vytsalo Please can you answer to my questions here in comments. Thanks! – VBoka Jan 19 '20 at 16:54

SQL's CHARINDEX Syntax exception

1 Answers1