Обсуждение: Underscores in column names

I have a underscores in most all of the column names in this database.
I've ran into a problem where Postgres doesn't like them.

SELECT * FROM "NATAB" WHERE
"NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'

Produces the error : "ESCAPE string must be empty or one character"

The column "NA_COLON" is a column that holds the numeric position at
which the first name ends and the last name begins. If the column name
does not contain an underscore it works fine. Any suggestions?

- Mark

Hi, is there a way to enter integer data by their HEX
representation??

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

On Friday 07 Mar 2003 3:58 pm, Mark Mitchell wrote:
> I have a underscores in most all of the column names in this database.
> I've ran into a problem where Postgres doesn't like them.
>
> SELECT * FROM "NATAB" WHERE
> "NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
> SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'
>
> Produces the error : "ESCAPE string must be empty or one character"
>
> The column "NA_COLON" is a column that holds the numeric position at
> which the first name ends and the last name begins. If the column name
> does not contain an underscore it works fine. Any suggestions?

Are you sure you don't mean substr() rather than substring()? I think the form
you're using does a POSIX regexp match and uses the third param as an escape
character.

--  Richard Huxton

You are 100% correct Rich. I changed the query to use substr() instead
of substring() and it works fine. Thanks for your quick answer.

- Mark

On Fri, 2003-03-07 at 11:28, Richard Huxton wrote:
> On Friday 07 Mar 2003 3:58 pm, Mark Mitchell wrote:
> > I have a underscores in most all of the column names in this database.
> > I've ran into a problem where Postgres doesn't like them.
> >
> > SELECT * FROM "NATAB" WHERE
> > "NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
> > SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'
> >
> > Produces the error : "ESCAPE string must be empty or one character"
> >
> > The column "NA_COLON" is a column that holds the numeric position at
> > which the first name ends and the last name begins. If the column name
> > does not contain an underscore it works fine. Any suggestions?
> 
> Are you sure you don't mean substr() rather than substring()? I think the form 
> you're using does a POSIX regexp match and uses the third param as an escape 
> character.
> 
> -- 
>   Richard Huxton
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html

Achilleus Mantzios wrote:
> Hi, is there a way to enter integer data by their HEX
> representation??
> 

Is this what you want?

regression=# select x'ffff'::int4; int4
------- 65535
(1 row)


Joe

Mark Mitchell <mark@lapcrew.com> writes:
> SELECT * FROM "NATAB" WHERE
> "NATAB"."NA_LAST_NAME" LIKE 'MITCHELL%' AND
> SUBSTRING("NATAB"."NA_NAME",0,"NATAB"."NA_COLON") LIKE 'MARK%'

> Produces the error : "ESCAPE string must be empty or one character"

This is a datatype issue, not a naming issue.  If the second and third
arguments to substring() aren't integers (and no, I don't mean numeric,
nor float, I mean int4), the parser is likely to think you are asking
for the three-text-parameter variant of substring().  Which is
SQL99-style regexps with an alternate escape string.
        regards, tom lane

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> Hi, is there a way to enter integer data by their HEX
> representation??

I'm not sure that this is SQL-spec, but at least as of 7.3, you can
coerce a bitstring literal to int, so:

z=# select x'0f';?column?
----------00001111
(1 row)

z=# select x'0f'::int4;int4
------  15
(1 row)

Looks like it works for int8 as well.
        regards, tom lane

On Fri, 7 Mar 2003, Tom Lane wrote:

> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > Hi, is there a way to enter integer data by their HEX
> > representation??
> 
> I'm not sure that this is SQL-spec, but at least as of 7.3, you can
> coerce a bitstring literal to int, so:
> 
> z=# select x'0f';
>  ?column?
> ----------
>  00001111
> (1 row)
> 
> z=# select x'0f'::int4;
>  int4
> ------
>    15
> (1 row)

Thanx.

> 
> Looks like it works for int8 as well.
> 
>             regards, tom lane
> 

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

On Fri, 7 Mar 2003, Joe Conway wrote:

> Achilleus Mantzios wrote:
> > Hi, is there a way to enter integer data by their HEX
> > representation??
> > 
> 
> Is this what you want?
> 
> regression=# select x'ffff'::int4;
>   int4
> -------
>   65535
> (1 row)

Sure. Thanx.

> 
> 
> Joe
> 

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr