Member Avatar for jen140

Hello all, i would like to know how it is possible to remove for example last 20 characters, or by specifing the string to remove.
For example i have 2 rows (in reality there are more than 100, thats why im not working it out by hand) with the next content:
insert into abc (1,"hello <b>b</b>lol");
insert into abc(2,"bye <b>a</b>lol");
i would like to remove 3 last characters or the string "lol".
Thanks in advance.
Have a good day.

  • 2 Contributors
  • 4 Replies
  • 185 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

Useful follow-up and safe workflow (builds on finding and 's hint)

If you only want to drop a trailing substring when it actually appears at the end, don’t use a global substitution function that will change every occurrence inside a value. Instead: make a quick backup, preview the result, then run a guarded update (or use a transaction so you can roll back).

Preview (returns the original and the proposed value without changing data):

SELECT id,
  text,
  CASE
    WHEN RIGHT(text, CHAR_LENGTH('suffix')) = 'suffix'
    THEN LEFT(text, GREATEST(CHAR_LENGTH(text) - CHAR_LENGTH('suffix'), 0))
    ELSE text
  END AS new_text
FROM abc
WHERE text LIKE CONCAT('%', 'suffix');

Notes: use CHAR_LENGTH (not LENGTH) if your data may contain multibyte characters; GREATEST(...,0) prevents negative lengths for short values.

Safe update pattern (run only after verifying the preview):

START TRANSACTION;
UPDATE abc
SET text = CASE
  WHEN RIGHT(text, CHAR_LENGTH('suffix')) = 'suffix'
  THEN LEFT(text, GREATEST(CHAR_LENGTH(text) - CHAR_LENGTH('suffix'), 0))
  ELSE text
END
WHERE text LIKE CONCAT('%', 'suffix');
COMMIT;

If you are on MySQL 8+, a regex-based approach can be shorter (anchoring to the end with $), and for very large tables update in batches (LIMIT + ORDER BY id) to avoid long locks. Also: back up before bulk edits, and remember that a simple replace-style function will modify all occurrences, not just trailing ones — that distinction is why the preview step is essential.

Member Avatar for jen140

Dont know why cant i edit the post,
but the querrys arent right, they should be :
insert into abc(id,text) values(1,"hello <b>b</b>lol");
insert into abc(id,text) values(2,"bye <b>a</b>lol");

Member Avatar for nav33n

Hi, can you elaborate your question ?
If you are inserting the value to the table from php, you can use substr to insert only a part of a string. If you are directly inserting the record to the table, you can use mysql's substring function.
If this solves your problem, well and good.. If it doesn't, please explain your question in detail.
P.S you can only edit your post within 30 mins of posting! :)

Cheers!

Member Avatar for jen140

Thanks for response, i've found what i was looking for, and it was mysql's replace function =).
What i neded to do was to remove part of the data in a row that repeated itself once for a row and many times for a column.
so i just neded to use next:
update abc set text=replace(text,'lol','NOTLOL') where 1;
and it would do what i neded.

Member Avatar for nav33n

Cool ! Congrats :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.