Reputation: 19882
I have a table of Categories.
I have records like this
id | name | symbol
1 | Feeders | FEE
2 | Ovens |
3 | Routers |
I have many records in this table. I want to update symbol field with category name like this that get the category name , extract 3 random characters (must be in order and always choose the first character with 2 random) then convert them into uppercase and apply update. How can i do that.
For example i want ovens to be updated with OVE or OVN or OVS or OEN or OES etc.
How can i do this in one query.
This is what i have tried but it sometimes brings 1 character sometimes 2 and sometimes 3
UPDATE category
SET symbol = (select
upper(SUBSTRING(name, 1+ FLOOR(RAND()*LENGTH(name)),3))
from default_category
where category_id = 2)
where category_id = 2
Upvotes: 1
Views: 1556
Reputation: 49089
You could use a query like this to calculate a random value for the symbol:
SELECT
id,
name,
UPPER(CONCAT(
LEFT(name,1),
SUBSTRING(name, FLOOR(r1), 1),
SUBSTRING(name, FLOOR(FLOOR(r1) + rand()*(LENGTH(name) - r1 DIV 1) + 1), 1))) symbol
FROM (
SELECT
id,
name,
case when LENGTH(name)<=3 THEN 2 ELSE rand()*(
LENGTH(name)-2)+2 END r1
FROM
categories
) s;
and this could be your UPDATE query:
UPDATE
categories INNER JOIN (
SELECT
id,
name,
UPPER(CONCAT(
LEFT(name,1),
SUBSTRING(name, FLOOR(r1), 1),
SUBSTRING(name, FLOOR(FLOOR(r1) + rand()*(LENGTH(name) - r1 DIV 1) + 1), 1))) symbol
FROM (
SELECT
id,
name,
case when LENGTH(name)<=3 THEN 2 ELSE rand()*(
LENGTH(name)-2)+2 END r1
FROM
categories
) s
) symb
ON categories.id=symb.id
SET
categories.symbol = symb.symbol
please see fiddle here.
Upvotes: 1
Reputation: 62861
Is this what you're looking for?
UPDATE YourTable
SET symbol = UPPER(LEFT(name,3))
Upvotes: 2