| Bug #13076 | Allow mass change of text to upper/lower case | ||
|---|---|---|---|
| Submitted: | 8 Sep 2005 21:37 | Modified: | 28 May 2009 11:40 |
| Reporter: | Robin Schumacher | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Query Browser | Severity: | S4 (Feature request) |
| Version: | 1.1.14 | OS: | Windows (Win XP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | workbench | ||
[8 Sep 2005 21:37]
Robin Schumacher
[9 Sep 2005 15:19]
J Jorgenson
This should be a request beyond the scope of SQL Browser...
USE SQL to perform this request...
ex:
UPDATE <tablename_here>
SET <columnName_here> = TOUPPER(<columnName_here)
IF you want to NOT modify the data, then in your SELECT, use TOUPPER() or TOLOWER() on the columns of concern.
[9 Sep 2005 15:32]
Robin Schumacher
Sorry - I don't think I explained the situation well. A developer may need to change a large block of SQL text to upper or lower case. For example, let's say I have lower case table names that I need to reference in a large proc, but the developer wrote everything in upper case as so:
CREATE PROCEDURE CORPORATE_ANALYSIS
(IN START_DATE DATETIME,
IN END_DATE DATETIME)
BEGIN
/*
display brokers ordered by highest commissions for time period
*/
SELECT A.BROKER_ID,
A.BROKER_FIRST_NAME,
A.BROKER_LAST_NAME,
SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
FROM BROKER A,
CLIENT_TRANSACTION B
WHERE B.TRANSACTION_COMP_TIMESTAMP BETWEEN START_DATE AND END_DATE AND
A.BROKER_ID = B.BROKER_ID
GROUP BY A.BROKER_ID,
A.BROKER_FIRST_NAME,
A.BROKER_LAST_NAME
ORDER BY 4 DESC;
/*
-- display offices ordered by highest commissions for time period
*/
SELECT C.OFFICE_NAME,
SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
FROM BROKER A,
CLIENT_TRANSACTION B,
OFFICE_LOCATION C
WHERE B.TRANSACTION_COMP_TIMESTAMP BETWEEN START_DATE AND END_DATE AND
A.BROKER_ID = B.BROKER_ID AND
A.OFFICE_LOCATION_ID = C.OFFICE_LOCATION_ID
GROUP BY C.OFFICE_NAME
ORDER BY 2 DESC;
/*
-- display top twenty investments ordered by highest invested dollars for time period
*/
SELECT B.INVESTMENT_VENDOR,
B.INVESTMENT_NAME,
SUM(PRICE) * SUM(NUMBER_OF_UNITS) TOTAL_INVESTED_DOLLARS
FROM CLIENT_TRANSACTION A,
INVESTMENT B
WHERE A.TRANSACTION_COMP_TIMESTAMP BETWEEN START_DATE AND END_DATE AND
B.INVESTMENT_ID = A.INVESTMENT_ID AND
A.ACTION = 'BUY'
GROUP BY B.INVESTMENT_VENDOR,
B.INVESTMENT_NAME
ORDER BY 3 DESC
LIMIT 20;
/*
-- display top investments types ordered by highest invested dollars for time period
*/
SELECT C.INVESTMENT_TYPE_NAME,
SUM(PRICE) * SUM(NUMBER_OF_UNITS) TOTAL_INVESTED_DOLLARS
FROM CLIENT_TRANSACTION A,
INVESTMENT B,
INVESTMENT_TYPE C
WHERE A.TRANSACTION_COMP_TIMESTAMP BETWEEN START_DATE AND END_DATE AND
B.INVESTMENT_ID = A.INVESTMENT_ID AND
C.INVESTMENT_TYPE_ID = B.INVESTMENT_TYPE_ID AND
A.ACTION = 'BUY'
GROUP BY C.INVESTMENT_TYPE_NAME
ORDER BY 2 DESC;
/*
-- display top twenty clients ordered by highest invested dollars for time period
*/
SELECT B.CLIENT_FIRST_NAME,
B.CLIENT_LAST_NAME,
SUM(PRICE) * SUM(NUMBER_OF_UNITS) TOTAL_INVESTED_DOLLARS
FROM CLIENT_TRANSACTION A,
CLIENT B
WHERE A.TRANSACTION_COMP_TIMESTAMP BETWEEN START_DATE AND END_DATE AND
B.CLIENT_ID = A.CLIENT_ID AND
A.ACTION = 'BUY'
GROUP BY B.CLIENT_FIRST_NAME,
B.CLIENT_LAST_NAME
ORDER BY 3 DESC
LIMIT 20;
END;
So the developer wants to easily change the code to be:
create procedure corporate_analysis
(in start_date datetime,
in end_date datetime)
begin
/*
display brokers ordered by highest commissions for time period
*/
select a.broker_id,
a.broker_first_name,
a.broker_last_name,
sum(broker_commission) total_commissions
from broker a,
client_transaction b
where b.transaction_comp_timestamp between start_date and end_date and
a.broker_id = b.broker_id
group by a.broker_id,
a.broker_first_name,
a.broker_last_name
order by 4 desc;
/*
-- display offices ordered by highest commissions for time period
*/
select c.office_name,
sum(broker_commission) total_commissions
from broker a,
client_transaction b,
office_location c
where b.transaction_comp_timestamp between start_date and end_date and
a.broker_id = b.broker_id and
a.office_location_id = c.office_location_id
group by c.office_name
order by 2 desc;
/*
-- display top twenty investments ordered by highest invested dollars for time period
*/
select b.investment_vendor,
b.investment_name,
sum(price) * sum(number_of_units) total_invested_dollars
from client_transaction a,
investment b
where a.transaction_comp_timestamp between start_date and end_date and
b.investment_id = a.investment_id and
a.action = 'buy'
group by b.investment_vendor,
b.investment_name
order by 3 desc
limit 20;
/*
-- display top investments types ordered by highest invested dollars for time period
*/
select c.investment_type_name,
sum(price) * sum(number_of_units) total_invested_dollars
from client_transaction a,
investment b,
investment_type c
where a.transaction_comp_timestamp between start_date and end_date and
b.investment_id = a.investment_id and
c.investment_type_id = b.investment_type_id and
a.action = 'buy'
group by c.investment_type_name
order by 2 desc;
/*
-- display top twenty clients ordered by highest invested dollars for time period
*/
select b.client_first_name,
b.client_last_name,
sum(price) * sum(number_of_units) total_invested_dollars
from client_transaction a,
client b
where a.transaction_comp_timestamp between start_date and end_date and
b.client_id = a.client_id and
a.action = 'buy'
group by b.client_first_name,
b.client_last_name
order by 3 desc
limit 20;
end;
Both Quest TOAD and Embarcadero DBArtisan offer a toolbar option to change the selected code case. It's used quite a lot.
--Robin
[9 Sep 2005 19:32]
Robin Schumacher
Just one quick note - upper/lower case enhancement submitted to Quest as it is in the Oracle version and not the current MySQL version.
[28 May 2009 11:40]
Susanne Ebrecht
Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. We won't add this feature request anymore. More informations about MySQL Workbench you will find here: http://dev.mysql.com/workbench/
