dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted July 9, 2018

WEBINAR:
On-Demand

How to Help Your Business Become an AI Early Adopter


Displaying Leading and Trailing Whitespace on Varchar Columns in MySQL

By Rob Gravelle

I was perusing the new questions on dba.stackexchange.com when I happened upon a question on How to see special characters in MySQL Workbench query results. Intrigued, I checked it out. The user was looking for a way to show trailing whitespace characters in order to see which values had extra spaces. Trailing whitespace is not always a bad thing; depending on what the data is used for, they may be perfectly acceptable. That got me thinking, how does one view these extra space characters? Since I could not find an option to do that in any of my GUI MySQL tools, I decided to follow the user's own workaround and see what it might take to inject the quotes within a SELECT statement.

It's fairly trivial to do so using the CONCAT() function as long as you know which fields you want to enclose in quotes. Coming up with a more generic solution takes a little more doing. In this tutorial, we'll look at two approaches, one employing a user function, the other, using a stored procedure.

Solution 1: Using a Function

The INFORMATION_SCHEMA COLUMNS table contains information about every column of every schema. It's referenced by the SHOW COLUMNS statement. Here's the output for the actor table of the Sakila sample database:

Field            Type         Null      Key     Default             Extra
-----------------------------------------------------------------------------------------------
actor_id         smallint(5)  unsigned  NO      PRI                 auto_increment
first_name       varchar(45)  NO			
last_name        varchar(45)  NO        MUL		
last_update      timestamp    NO                CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP

You can query the COLUMNS table directly to obtain specific information, such as the data type. The following user function accepts the table and column names to look up the column type:

CREATE DEFINER=`root`@`localhost` 
  FUNCTION `get_column_type`(p_table_name VARCHAR(50), p_column_name VARCHAR(50)) 
    RETURNS varchar(20) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN
  DECLARE p_column_type VARCHAR(20);
	
  SELECT DATA_TYPE 
  FROM   INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = 'sakila'
  AND    TABLE_NAME   = p_table_name 
  AND    COLUMN_NAME  = p_column_name into p_column_type;
  
  RETURN p_column_type;
END

We can then call get_column_type() for any field that we might want to modify based on its type:

SELECT 
  CASE 
    WHEN get_column_type('actor', 'actor_id') = 'VARCHAR' THEN
      CONCAT('"',actor_id,'"')
    ELSE
      actor_id
    END AS actor_id,
    CASE 
      WHEN get_column_type('actor', 'first_name') = 'VARCHAR' THEN
        CONCAT('"',first_name,'"')
      ELSE
        first_name
    END AS first_name,
    CASE 
      WHEN get_column_type('actor', 'last_name') = 'VARCHAR' THEN
        CONCAT('"',last_name,'"')
      ELSE
        last_name
    END AS last_name
FROM actor;

Here are the first five rows produced by the above query:

actor_id	first_name  last_name
-------------------------------------
1	        "PENELOPE"  "GUINESS"
2	        "NICK"      "WAHLBERG"
3	        "ED"        "CHASE"
4	        "JENNIFER"  "DAVIS"
5	        "JOHNNY"    "LOLLOBRIGIDA"

Solution 2: Using a Stored Procedure

The function approach works well enough for simple queries against smaller datasets, but calling the function for each column could get tedious fast. What we need is a stored procedure that can iterate over table columns and call the function for us!

Here is the basic proc. I'll explain a few things after the code:

CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `varchar_select`(IN p_schema_name varchar(50), 
                             IN p_table_name varchar(50))
    READS SQL DATA
BEGIN

  DECLARE p_sql VARCHAR(255) DEFAULT "SELECT ";
  DECLARE num_rows INT DEFAULT 0;
	DECLARE i INT DEFAULT 0;
  DECLARE col_name VARCHAR(50);
	DECLARE col_type VARCHAR(50);
	
  DECLARE col_names CURSOR FOR
  SELECT column_name, column_type
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = p_schema_name 
	AND table_name = p_table_name
  ORDER BY ordinal_position;
	
  -- be sure to open the cursor before calling FOUND_ROWS()!
  OPEN col_names;
  select FOUND_ROWS() into num_rows;
  
  SET i = 1;
  the_loop: LOOP
  
  IF i > num_rows THEN
    CLOSE col_names;
    LEAVE the_loop;
  END IF;
  
  FETCH col_names INTO col_name, col_type;     
  
  SET p_sql = CONCAT(p_sql, col_name, ',');
  
  SET i = i + 1;  
  END LOOP the_loop;
  
  -- remove the trailing comma from the field list
  SET p_sql = TRIM(TRAILING ',' FROM p_sql);
  
  -- create and execute the prepared statement
  SET @sqlv = p_sql;
  PREPARE stmt1 FROM @sqlv;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1;
END

The above version of the proc iterates over the column names and concatenates them to the SELECT string that will be executed as a prepared statement. Now, we'll add the code to enclose the VARCHAR values within quotes.

The Updated Call to Concat()

The col_type includes the length of varchar fields so that it may come back as varchar(50), so a LIKE expression is employed for type matching.

The 2nd CONCAT parameter is a string that calls CONCAT() from the prepared statement. Hence, it produces a string such as CONCAT('"', col_name, '"') AS col_name,.

SET p_sql = IF(col_type LIKE 'varchar%', 
              CONCAT(p_sql, 'CONCAT(\'"\',',col_name, ',\'"\') AS ',col_name, ','),
              CONCAT(p_sql, col_name, ',')
            );

Calling the varchar_select Proc

Calling our proc for the actor table does exactly what we hope it would; the name fields are enclosed within quotes, while the other fields are not:

mysql> call varchar_select('sakila', 'actor');

actor_id      first_name  last_name       last_update
-------------------------------------------------------------
1             "PENELOPE"  "GUINESS"       2006-02-15 04:34:33
2             "NICK"      "WAHLBERG"      2006-02-15 04:34:33
3             "ED"        "CHASE"         2006-02-15 04:34:33
4             "JENNIFER"  "DAVIS"         2006-02-15 04:34:33
5             "JOHNNY"    "LOLLOBRIGIDA"  2006-02-15 04:34:33

The full function and proc code is available on GitHub.

Going Forward

While we have achieved something fairly generic today, there are still some limitations to our proc. For one thing, it always returns all of the table columns. Moreover, there is no support for joins at all. In the next installment, we'll take a parameterized query approach to handle more complex queries.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM