CODEWITHSUNDEEP
phpmysqlsqlselect
SLEDGE
SLEDGE

Reputation: 63

SQL Select All Columns With Character Limit

I'm making a custom script similar to phpMyAdmin.

I need to use the asterisk (*) in order to select all the data in a table without yet knowing the columns. The trick is that I have to limit the amount of data retrieved from each column for efficiency. Basically do this,

SELECT SUBSTR(Username, 20) FROM Users LIMIT 10

Which limits the Username column to 20 characters, but I have to do it all at the same time like this,

SELECT SUBSTR(*, 20) FROM Users LIMIT 10

How do I select all my data with a specific character limit on each column while using an asterisk?

Upvotes: 1

Views: 3383

Answers (4)

John Woo
John Woo

Reputation: 263933

Another alternative is to create a Dynamic SQL, the only things you need are

  • Database name
  • Table name

Here's the query:

SET @db = 'db_2_a8d87';   -- database name
SET @tb = 'Table1';       -- table name
SET @sql = NULL;
SELECT GROUP_CONCAT(CONCAT('SUBSTRING(',column_Name,',1,3)', column_Name)) INTO @sql
FROM   information_schema.columns
WHERE  table_schema = @db AND table_name = @tb;

SET @sql = CONCAT('SELECT ', @sql, ' FROM ', @tb);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 2

King Skippus
King Skippus

Reputation: 3826

As an alternative, you can do this in two parts. For the first part, run:

SHOW COLUMNS IN Users;

Then use the results in the Field column of that query to programmatically generate a second query selecting each of the column names returned in the first query.

Upvotes: 1

Adeel Ahmed
Adeel Ahmed

Reputation: 1601

SELECT SUBSTR(COL1,20), SUBSTR(COL2,20),SUBSTR(COL3,20), .... FROM Users LIMIT 10

You have to limit each column explicitly..

Upvotes: 2

Michael
Michael

Reputation: 920

You have to specify all the columns you wish to retreive.

SELECT SUBSTR(Username,20) as user, SUBSTR(Column2,20) as col, FROM Users LIMIT 10

You can't use an asterisk in a mysql function the way you want. Each column has to be limited.

Upvotes: 2

Related Questions