how can i get the tablename when i have a query output from cfquery for each columns, i can do "tbl.column1", but i don't want to use that, is there any internal java function where i can get the tablename of the column i searched google but could not find something relevant for MYSQL, i tried cfdbinfo, but it needs table but nothing for a query where i have some joins to different tables? any clues experts

Could you use an alias for each column and include the table name in the alias?

Yeah, beyond that, I don't think you can. If you think of SQL in a sort of OOP kind of way, the selected data is a public property. The table that data came from is a private implementation detail.

Can you explain the problem that you are trying to solve? Why do you need to know the table name. As Tim says, how/where data is persisted is an implementation detail.

i have a query like this

select tblItems.ItemsID as Items_ID,
	tblItems.title AS ItemsTitle,
  tblItems.ItemsManager_ContactID,
  CONVERT(tblItems.EndDate_Scheduled ,date) as EndDate_Scheduled,
  tblview.ViewName as viewName 
  from tblItems 
  LEFT OUTER JOIN tblviews ON (tblItems.Items_viewsID = tblviews.viewsID) 
  LEFT OUTER JOIN tblcontact ON (tblItems.ItemsManager_ContactID = tblcontact.ContactID) 
  WHERE (tblItems.ItemsStatusID = 7) ORDER BY tblItems.EndDate_Scheduled ASC, 
  Items_viewsID ASC limit 1

OK, and why do you need to know anything about the table the data came from?

because i am building it as an Array and when the freaking delimter comes in query, it breaks

Can you demonstrate that piece? I think we're still unsure why you need to know the table name.

which piece needs explanation, i can try again,

Sorry for not following. So you run that query and get a CF query object back.

which column contains a delimiter? And that doesn't explain why you need the table name. What's the overall problem you're attempting to solve?

You then want to convert that query into an array of structs (one element in the array for each cf query object row)?

You should either be able to specify the delimter when using array or list functions, or

replace()

the delimiter in your query. I'm not familiar with mySQL specifically, but sure it has replace functionality.

ok, wait, here is my function as to what i am doing https://trycf.com/gist/0cd6e49b5754069eb07f35818f09e913/lucee5?theme=monokai

Which column is causing the problem? I don't see any numbers being formatted in the query you included.

tblItems.ItemsID as Items_ID,
	tblItems.title AS ItemsTitle,
  tblItems.ItemsManager_ContactID,
  CONVERT(tblItems.EndDate_Scheduled ,date) as EndDate_Scheduled,
  tblview.ViewName as viewName

For whatever reason you trying to parse SQL query, you should not it do it in such straightforward way. Do you try to create SQL compiler or something? I think you have to share your business task first.

If you are trying to find out what columns you have in your query then there are a couple of ways to do that without needing to try and parse an SQL string. For example: https://trycf.com/gist/931a91db83f59367a2731d755da0d65c/acf2021?theme=monokai

i am using a Format function from mysql to format a number

Why are you formatting numbers in the storage tier? This is - almost always - a job for the presentation tier. But more importantly...

For whatever reason you trying to parse SQL query [...]

Yeah to me I'm asking myself "WTH are you actually trying to do here?" Because what yer doing looks to be fundamentally the wrong approach to [anything]. So... back up quite a long way. What are you trying to achieve that involves you trying to parse and SQL SELECT statement for column names?!?

@aliaspooryorik how can i get the tablename, i need tablename i also explored getmetadat and even i see the function gettablename but it does not show up

Why do you want the table name? What are you doing that makes you think you need to be able to re-get the table name from a SELECT statement resultset? Also... you ran the SELECT statement in the first place, right? How do you not already know the table names?

You can't get the tablename as parsing SQL is only going to lead to tears and pain I'm afraid. I know we are being boring, but can you explain why you need the table name. If you absolutely must have it then stick the table name into some aliased field, so your SQL select will become:

select
  'MYTABLE' as 'the_source_table',
  tblItems.ItemsID as Items_ID,
  tblItems.title AS ItemsTitle,
  ...

@aliaspooryorik Since there are JOINs involved (and thus, multiple tables), I think Myka's suggestion of including the table names in the column aliases might be a little less fragile (assuming Simone really does need the table names). I wholeheartedly agree with you about the folly of trying to parse the sql string.

@David Buck I was just chucking another idea into the mix, although I'm in the dark as to what the real problem we're trying to solve here is.

@aliaspooryorik Maybe Simone really does need the table names (it's hard to infer anything else from the information provided), in which case I suspect the very first answer is the best they're gonna get.