0

i am using MariaDB. i have a table with 50 columns. i want to create a simple query like SELECT * FROM myTable but the id column is a binary UUID which needs converting into human readable format, like this:

SELECT LOWER(CONCAT(
  SUBSTR(HEX(id), 1, 8), '-',
  SUBSTR(HEX(id), 9, 4), '-',
  SUBSTR(HEX(id), 13, 4), '-',
  SUBSTR(HEX(id), 17, 4), '-',
  SUBSTR(HEX(id), 21)
))
FROM myTable

the problem is, AFAIK, i now have to explicitly name each subsequent 49 columns if i want to get them all. now i know this is dumb, and i'm sure there is a correct way to do this but i can't find how to do what i want: simply get all 50 columns with this ONE modified SELECT. it striked me a something that is doable, regardless of how or which column needs modifying, but i haven't been able to find such information.

if this were just mySQL, i could use the built-in commands for managing UUIDs, but MariaDB doesn't have those; they didn't make it into the last update as they'd hoped. :P

so how can i build a query that does what i need it to do?

EDIT: for the knucklehead that flagged this with 'already answered', READ THE TITLE. I do NOT want to exclude a column, I want to MODIFY one. >_>

WhiteRau
  • 818
  • 14
  • 32
  • [Why is SELECT * considered harmful?](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful#:~:text=SELECT%20*%20exposes%20your%20system%20to,or%20present%20that%20new%20data.) – Luuk Apr 22 '21 at 17:42
  • You could do: `select concat('SELECT ',group_concat(column_name),' FROM myTable') from information_schema.columns where table_name='myTable';` and then do some copy/paste on the result. – Luuk Apr 22 '21 at 17:46
  • 2
    Use `SELECT *, {expression} AS readable_UUID FROM ...`. This will give 51 columns. Then simply ignore `UUID` column on the client side. Or hide 1st column on the grid. – Akina Apr 22 '21 at 17:56
  • @Akina I didn't know you could just ADD columns after the '*'. I always thought it was only usable by itself. that's the way to go. thank you very much. – WhiteRau Apr 22 '21 at 19:01

0 Answers0