Thread: 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