How to Select some fields have same prefix Names

Question

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

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

Is it possible to do a request like :

SELECT 'PREFIX_*' FROM mytable;

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 USER.* FROM USERS

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

 - USER_NickName
 - USER_Password

WHERE they have the same PREFIX NAME !!
i know that my Query SELECT Above is Wrong, but is there a way to SELECT Fields without writing All fields names using just the Prefix they have ?
...
Very IMPORTANT Note:
//========================================================//
// < PLZ: My QUESTION IS About FIREDAC and Not About Native SQL Statements ..!! > //
//========================================================//

Just using some logic, if you just use the prefix how it will determine which column you want the Query to be applied? — SIMBIOSIS surl, Aug 22 '21 at 09:33
@AmigoJack In Sql, it is perfectly valid to specifu a subset of columns in a Select. — MartynA, Aug 22 '21 at 09:38
@Olivier I TRY IT and it works for me without any ERRORS ..., but the query must be first like that `SELECT USERS.* FROM USERS` and this will load all fields in that table — Roberto, Aug 22 '21 at 09:39
How do you reckon your Table has 5 columns? It has seven, ID..Picture. — MartynA, Aug 22 '21 at 09:39
@Roberto that is still filtering datasets, not columns. @MartynA yes: specify. But not filter. `SELECT t.di*k FROM table AS t` is not allowed in any DBMS I ever encountered. — AmigoJack, Aug 22 '21 at 09:54
@Roberto you can use dbschema’s te get the filednames of a table. But that’s dbvendor specific. A simple way to get info about the fields/types of a tabel is to use `select top 0 * from table` - works with de sqlbased db vendors I’ve used so far. — R. Hoek, Aug 22 '21 at 10:16
I have not checked but it may be that FDac allows the identifier before the dot because it assumes that it is a table alias which will be resolved by the RDMS. — MartynA, Aug 22 '21 at 10:20
@AmigoJack: Is what yu are meaning to say is that SQL does not do pattern-matching to resolve wild-carded partial column names? — MartynA, Aug 22 '21 at 10:23
@MartynA Yes: only `SELECT t.* FROM table AS t` is allowed to get ALL columns from that table - otherwise you have to fully SPECIFY the columns you want. Short form: `SELECT * FROM table` — AmigoJack, Aug 22 '21 at 12:14
@R.Hoek and how are you able to combine that info into only selecting specific columns? I mean: if we want to know the table's column names we can query the table itself - that still doesn't help us to FILTER BY column name. — AmigoJack, Aug 22 '21 at 12:19
I would suggest using a SQL IDE that does code completion and expansion either by itself or with add-ins. Write and test queries there before using them in your Delphi projects. — Brian, Aug 22 '21 at 12:34
Wait - why does this question have identical text and examples just like [this Q from almost 11 years ago](https://stackoverflow.com/q/3941156/4299358)? — AmigoJack, Aug 22 '21 at 21:05
Does this answer your question? [How to select columns that have name beginning with same prefix?](https://stackoverflow.com/questions/3941156/how-to-select-columns-that-have-name-beginning-with-same-prefix) — Delphi Coder, Aug 22 '21 at 21:11
@AmigoJack Simply, Because my Question is BASED from that Very OLD Question, where my is Universal and while the VERY OLD is talks About PostgreSQL and Specific Version !! — Roberto, Aug 23 '21 at 11:16
@DelphiCoder Unfortunatly Not !!, and that was just a Base IDEA of my Universal Question.. — Roberto, Aug 23 '21 at 11:17
@AmigoJack with the returned info the OP can create a SQL dynamically. Like `var sql = 'SELECT ' + GetFieldNamesWithPrefix('PREFIX_','Table') + ' FROM Table WHERE x = y` resulting into `SELECT PREFIX_Field1, PREFIX_Field2 FROM Table WHERE x = y` (note: `GetFieldNamesWithPrefix` should return the required fields using the method described by me) - but to be clear, there's no way sql will understand wildcard fieldnames: it's just the `*` for all fields OR the specific fields specified by name. — R. Hoek, Aug 23 '21 at 14:26

Roberto · Answer 1 · 2021-08-23T14:19:18.947

-2

in Sqlite we can do this :

SELECT 
  "SELECT " || GROUP_CONCAT(NAME) || "  FROM USERS"
  FROM
  PRAGMA_TABLE_INFO("USERS")
  WHERE SUBSTR(NAME, 1, 5)= "USER_"

RESULT:

SELECT USER_NickName,USER_Password  FROM USERS

the Result Above is a Ready to use Query inside our Result Query Above
=======
i hope this Help to Solve All Firedac Databases that Support!!
=======
Unfortunately i have not tested with Others DataBases now..

edited Aug 23 '21 at 14:19

answered Aug 23 '21 at 12:17

Roberto

105
7

How is this an answer? You don't get any results from your table but instead only one dataset of text - that's what you could have achieved with `information_schema` already. You still don't actually query your table. – AmigoJack Aug 23 '21 at 13:54
@AmigoJack yes ofcourse, but like i sad in my Sub_Answer(not the Total Answer), the Result Above is a Ready to use Query inside our Result Query Above... – Roberto Aug 23 '21 at 14:06
Just repeating the text from your answer doesn't add any value. The Q is "select some fields", not "create query text". This answer's query is basically the same as [this old one](https://stackoverflow.com/a/45303354/4299358), just with [SQLite's `group_concat()` function](https://www.sqlite.org/lang_aggfunc.html#group_concat). Are you sure you know what you actually want? – AmigoJack Aug 23 '21 at 14:47
@AmigoJack should i Delete my Sub Answer while i can't Post like that Infos inside the COMMENT SECTION ABOVE – Roberto Aug 23 '21 at 14:53
1

I agree with @AmigoJack to the extent that this answer doesn't match the original question. However, the real reason your q and this answer have attracted so much discussion without reaching a definite conclusion is that the q is not sufficiently clear - you seemed to be expecting a solution which uses SQL to generate a SQL-executable query to deliver the final result, but did not definitively say so in your q. However, it is trivial to use Delphi code to construct a query which uses an arbitrary list of SELECT columns chosen by whatever criteria you like. – MartynA Aug 23 '21 at 15:23
@MartynA yes i totally Agree with both of you, but my SUB_Answer above i think is Able to be a Useful Info and an IDEA in order to solve my Question Above .. – Roberto Aug 23 '21 at 15:38

How to Select some fields have same prefix Names

1 Answers1

RESULT: