CODEWITHSUNDEEP
mysqlsql
Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Update Column with 3 character length uppercase string in mysql

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

Answers (2)

fthiella
fthiella

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

sgeddes
sgeddes

Reputation: 62861

Is this what you're looking for?

UPDATE YourTable
SET symbol = UPPER(LEFT(name,3))

Upvotes: 2

Related Questions