Selecting a field with special characters

[Ertan Küçükoglu]

Hello,

I am provided a FirebirdSQL 2.5.9 database. It has a table with a column name including a TAB character. I didn't even know that FirebirdSQL allowed special characters (at least some of them) in column names until I received that database.

I used below command to extract whole database DDL

isql -ex -u sysdba -p password localhost:<full path to my db>

Here is what I see for that column in the exported file.

As it can be seen, the last character in the column name is a TAB character.

I need to select that column by name something like below

select "SELF_MENU_TABLE_PICKUP\t" from mytable

select "SELF_MENU_TABLE_PICKUP" || CHAR(9) from mytable

etc.

But I could not make it work using such escape characters in select statements .

Is there a way to actually include this column name in select other than adding tab character itself?

Thanks & Regards,

Ertan Küçükoğlu

[Omacht András]

Ertan,

 

if you only want to use it in selects, then make a computed field that refers to it and use that in selects.

 

András

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4yegjHEBXW2pc88ZxtwCnsy7D8cpbnO%2BnuRbidV5f51j6A%40mail.gmail.com.

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 27.05.2023 20:27:
> Is there a way to actually include this column name in select other than adding
> tab character itself?

No. And that was the purpose of the database author, I guess: to make a
database that nobody can use outside of their application.

--
WBR, SD.

[Karol Bieniaszewski]

I do not understand your problem.

Do you have tab character on your keyoard? If Yes press it, but in software wich do not replace it with spaces, eg. Use Notepad.

select "SELF_MENU_TABLE_PICKUP       " FROM MYTABLE

 

Regards,

Karol Bieniaszewski

 

Od: Ertan Küçükoglu
Wysłano: sobota, 27 maja 2023 20:27
Do: firebird...@googlegroups.com
Temat: [firebird-support] Selecting a field with special characters

 

Hello,

 

I am provided a FirebirdSQL 2.5.9 database. It has a table with a column name including a TAB character. I didn't even know that FirebirdSQL allowed special characters (at least some of them) in column names until I received that database.

 

I used below command to extract whole database DDL

 

isql -ex -u sysdba -p password localhost:<full path to my db>

Here is what I see for that column in the exported file.

 

As it can be seen, the last character in the column name is a TAB character.

 

I need to select that column by name something like below

 

select "SELF_MENU_TABLE_PICKUP\t" from mytable

select "SELF_MENU_TABLE_PICKUP" || CHAR(9) from mytable

etc.

 

But I could not make it work using such escape characters in select statements .

 

Is there a way to actually include this column name in select other than adding tab character itself?

 

Thanks & Regards,

Ertan Küçükoğlu

--

[Tim Crawford]

I think he is saying the column NAME has a tab character.

Weird

Tim

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4QTCxg3C1pzl79%40smtp.poczta.onet.pl.

[Karol Bieniaszewski]

Yes, exacly, tab character in column name, table name, whatever 😉

Email also replace tabs to space, so use some software which do not replace it, eg notepad, notepad++.

 

Regards,

Karol Bieniaszewski

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3glfrmi8r3peir4tu1fmvqd9.1685222694987%40gmail.com.

 

[Virgo Pärna]

On 28.05.2023 0:40, Karol Bieniaszewski wrote:
> Email also replace tabs to space, so use some software which do not
> replace it, eg notepad, notepad++.
>

I think, that importent part is using quotes around the name and
putting tab inside quotes. And in Notepad++ tab usage is configurable...
Important thing would be to make sure, that SQL tool does not do any tab
replacement. I could not enter this tab directly in Flamerobin, but
pasted sql from notepad worked.

--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Tim Crawford]

A tab characters is not a valid character to use for table or column names in any RDBMS
I know of so I don't know this could have happened,

In windows you can insert a tab character almost anywhere by keyboar Alt+009
(or any other ascii character for that matter)

This works in FlameRobin, though interestingly not in my email client...

I would try with single quotes, where the ^t below represents Alt+009

select 'SELF_MENU_TABLE_PICKUP^t' FROM MYTABLE

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4QTFZx2wTZz2K23x5%40smtp.poczta.onet.pl.

[Dimitry Sibiryakov]

Tim Crawford wrote 29.05.2023 13:37:
> A tab characters is not a valid character to use for table or column names in
> any RDBMS
> I know of so I don't know this could have happened,

Any character is valid in delimited identifiers according to SQL standard.

--
WBR, SD.

[Mark Rotteveel]

On 29-05-2023 13:37, Tim Crawford wrote:
> A tab characters is not a valid character to use for table or column
> names in any RDBMS
> I know of so I don't know this could have happened,

Firebird basically allows anything in delimited identifiers (though, I
haven't checked too closely if there are characters which are excluded).

Whoever created this database seems to have used it intentionally to
make it hard to access.

> In windows you can insert a tab character almost anywhere by keyboar Alt+009
> (or any other ascii character for that matter)
>
> This works in FlameRobin, though interestingly not in my email client...
>
> I would try with single quotes, where the ^t below represents Alt+009
>
> select 'SELF_MENU_TABLE_PICKUP^t' FROM MYTABLE

Single quotes will make it a string literal, not a column name. It must
be enclosed in double quotes to make it a valid delimited identifier.

Mark
--
Mark Rotteveel