Question 353777 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL statement to get the correct category without digits

i have a column category which has the following data:

0100 Single Function Inkjet Printers
0100 Wireless Phones
400 optics (421)
4000 sound and recording (267)
80000 electrical & heating (60)
81000 plumbing
Accessories
Action Figures/Collectibles

I am trying to create a category calculated field in SQL which will have only the text:

Single Function Inkjet Printers
Wireless Phones
optics
sound and recording
electrical & heating 
plumbing
Accessories
Action Figures/Collectibles

I have tried using a combination of left, right, trim and substring functions to do so, but havent been successfull. Any help would be appreciated.

Tags:

2

Answers


  1. 0

    If you’re using MySQL 8.0 or higher this can be done using regex_replace:

    ALTER TABLE data
ADD COLUMN calc_val varchar(50)
GENERATED ALWAYS AS (TRIM(REGEXP_REPLACE(val, '[(]?[0-9]+[)]?', '')));

    This example replaces numbers with optional round brackets, if you have any other number formats that needs to be replaced – see the regex manual.

    Demo:
    https://dbfiddle.uk/d8Aapd5j

    Login or Signup to reply.
  2. 0 
    CREATE TABLE test (
  raw VARCHAR(255),
  cleaned VARCHAR(255) AS (TRIM(REGEXP_REPLACE(raw, '\(?\d+\)? *', '')))
);
INSERT INTO test (raw) VALUES 
('0100 Single Function Inkjet Printers'),
('0100 Wireless Phones'),
('400 optics (421)'),
('4000 sound and recording (267)'),
('80000 electrical & heating (60)'),
('81000 plumbing'),
('Accessories'),
('Action Figures/Collectibles');
SELECT * FROM test;
    raw cleaned
    0100 Single Function Inkjet Printers Single Function Inkjet Printers
    0100 Wireless Phones Wireless Phones
    400 optics (421) optics
    4000 sound and recording (267) sound and recording
    80000 electrical & heating (60) electrical & heating
    81000 plumbing plumbing
    Accessories Accessories
    Action Figures/Collectibles Action Figures/Collectibles

    fiddle

    TRIM() provides leading space removing when the substring to be deleted is a prefix.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search