What Does the Colon Sign ":" Do in a SQL Query

What does the colon sign : do in a SQL query?

That's called a bind variable in Oracle.

what's the name for ":"?

Colon.

What does a colon (':') mean in SQL syntax?

It is a bind variable:

A placeholder in a SQL statement that must be replaced with a valid
value or value address for the statement to execute successfully. By
using bind variables, you can write a SQL statement that accepts
inputs or parameters at run time. The following example shows a query
that uses v_empid as a bind variable:

What does 'colon' mean in SQL under WHERE clause?

First, the || operator is a string concatenation operator. So it looks like the code is building a WHERE clause using conditions specified by cLF. Though I'm not entirely sure why they're tacking on cLF three times there.

The :key syntax refers to a parameter in a parameterized query. Its value will be passed in when the SQL statement you're building is actually run.

What does a colon before a literal in an SQL statement mean?

The colon is a common character that indicates a placeholder for a variable value in a SQL statement. In this case, the those placeholders are getting replaced by the value of userId and project_id at runtime. This is great for avoiding SQL injection vulnerabilities.

SQL(ite) where clause with a colon (:) character

You would seem to need single quotes for the string constant:

SELECT *
FROM PhoneStatus
WHERE phoneDeviceId = '00:66:4B:B2:7B:F5'

If the query is delimited by single quotes, then double them up or escape them somehow.

How to escape colon `:` within a native SQL query in Hibernate?

In Hibernate, escaping is done with prepending by \. But in Java, you also have to escape \ by another \. So every : needs to be replaced by \\:. Finally, you get:

Query query = session.createSQLQuery("SELECT
    XMLSERIALIZE
    (CONTENT
      XMLELEMENT
      (
        NAME \"ltc\\:DOAATLTC\",
        XMLATTRIBUTES
        (
          'http://www.edftrading.com/Trade/Common/DoaatLTC' AS \"xmlns\\:ltc\",
          'http://www.edftrading.com/Trade/Common/DoaatLTCHourlyNomination' AS \"xmlns\\:ltchnom\"
        ),
        XMLELEMENT ( ... ) FROM ...");

SQL Server query Where string has ':' colon in it returns not found

The issue here should be that you convert the PictureOwner to VARCHAR, which will truncate your string. Try to specify the corresponding length like VARCHAR(255)

Escaping the colon character ':' in JPA queries

I'm not aware of a standard way to escape a colon character in a query that is obviously interpreted as a named parameter prefix, and thus confuses the query parser.

My suggestion would be to create and use SQL functions if possible. Depending on your provider, there might be other options (like using another character and substituting the chosen character by a : in an interceptor) but at least the previous suggestion would keep your JPA code portable across providers.

PS: if you're using Hibernate, there is a very old patch attached to HHH-1237.

Update: There is an "interesting" paragraph in the JPA 1.0 spec about named parameters and native queries:

3.6.3 Named Parameters

A named parameter is an identifier
that is prefixed by the ":" symbol.
Named parameters are case-sensitive.

Named parameters follow the rules for
identifiers defined in Section 4.4.1.
The use of named parameters applies to the Java Persistence query
language, and is not defined for
native queries. Only positional
parameter binding may be portably used
for native queries.

The parameter names passed to the
setParameter methods of the Query
API do not include the ":" prefix.

This won't really help you but your case is a strong hint that the ":" in native queries shouldn't even be considered (at least not without a way to escape it or disable it detection).

---

Related Topics