SQLite Forum

How to select columns that have name beginning with same prefix?
Login

How to select columns that have name beginning with same prefix?

(1) By Roberto (13011_DZ) on 2021-08-22 18:14:58 [link] [source]

Using SQLITE, is there a way to select a set of columns that have name beginning with same prefix.

Suppose we have columns : PREFIX_col1, PREFIX_col2, ...

Is it possible to do a request like :

SELECT 'PREFIX_*' FROM mytable; Which of course doesn't work.

(2.1) By curmudgeon on 2021-08-22 19:23:42 edited from 2.0 in reply to 1 [link] [source]

It's easy enough to get the SQL using

select 'select ' || group_concat(name) || ' from mytable' from pragma_table_info('mytable') where substr(name,1,7)='PREFIX_'

but I think you'd need the eval function to execute the SQL which takes you into load extension territory

https://sqlite.org/c3ref/load_extension.html

I'm not sure if eval is automatically loaded into the CLI and I don't have access to it at the moment and to be honest I'm not sure if eval can return multiple rows. You might need a user defined function to execute the SQL.

https://sqlite.org/c3ref/create_function.html

(3) By Roberto (13011_DZ) on 2021-08-22 19:58:41 in reply to 2.1 [link] [source]

please could you make for me a good Query that can work with my IDE

https://stackoverflow.com/questions/68879924/how-to-select-some-fields-have-same-prefix-names

i have this Table: [USERS] has 07 Fields:

  • ID (PRIMARY kEY)
  • USER.NickName
  • USER.Password
  • EMAIL.Recovery
  • REG.Insert_DateTime
  • REG.Edit_DateTime
  • PICTURE

I need to Select just TWO Fields using Query like that:

SELECT "SELECT " GROUP_CONCAT(name) " from USERS" "FROM PRAGMA TABLE_INFO('USERS')"

WHERE SUBSTR(NAME,1,5)="USER."

what i mean Above is to load into my Query just this TWO Fields:

  • USER.NickName
  • USER.Password

(4.1) By Roberto (13011_DZ) on 2021-08-22 20:07:31 edited from 4.0 in reply to 2.1 [link] [source]

SELECT "SELECT " GROUP_CONCAT(name) " from USERS"

FROM PRAGMA_TABLE_INFO("USERS") WHERE SUBSTR(NAME,1,5)="USER." +++++++++ BUT Still not work for me: https://res.cloudinary.com/bravesofts/image/upload/v1629662772/PREFIX.png

(5) By Larry Brasfield (larrybr) on 2021-08-22 20:10:10 in reply to 4.0 [source]

How is that unreadable (and likely unworkable) glop of text better than: SELECT "USER.NickName" as Nickname, "USER.Password" as Password FROM "USERS" ?

In other words, why go to the trouble of writing a longer and less readily comprehended query that requires an eval() step to work?

(6) By Roberto (13011_DZ) on 2021-08-22 20:21:02 in reply to 5 [link] [source]

i understand your Solution Mr:Larry

but what about a query that require 10 or 20 Columns from 50 or 100 columns ?

do i need to write All Fields Names 10 or 20 times for every query ?

(7) By Larry Brasfield (larrybr) on 2021-08-22 20:36:49 in reply to 6 [link] [source]

If you have a big pile of column names where meanings are encoded in portions of the names, then you would be better off devising a schema where that data can be incorporated into queries by less devious means.

(8) By Warren Young (wyoung) on 2021-08-23 00:08:16 in reply to 7 [link] [source]

Even with this naming issue aside, "50 or 100 columns" is a schema design smell. Such a table probably holds more than it should, so it would benefit from a pass or three of normalization.

(9.1) By John Dennis (jdennis) on 2021-08-23 02:03:32 edited from 9.0 in reply to 2.1 [link] [source]

You can do this in the CLI using .once and .read

.once /tmp/aaa.sql
select 'select ' || group_concat(name) || ' from mytable' from pragma_table_info('mytable') where substr(name,1,7)='PREFIX_';
.read /tmp/aaa.sql

(10) By curmudgeon on 2021-08-23 11:40:19 in reply to 9.1 [link] [source]

Nice one John.

(11) By anonymous on 2021-08-23 12:35:10 in reply to 9.1 [link] [source]

Simple but very effective , thanks for this hint how to use the CLI for dyn. generated SQL.

(12) By J.M. Aranda (JMAranda) on 2021-08-23 20:59:23 in reply to 1 [link] [source]

If you read an IBM DB2 manual you will see that it describes hundreds of options. 
At the end, in a colored box, it says: IBM recommends not using any of these options.
It is hilarious but it is an example of what are called "best practices".
Perhaps SQLite could also give that kind of suggestion in its documentation.