Strip last two characters of a column in MySQL

by

To select all characters except the last n from a string (or put another way, remove last n characters from a string); use the SUBSTRING and CHAR_LENGTH functions together:

SELECT col
     , /* ANSI Syntax  */ SUBSTRING(col FROM 1 FOR CHAR_LENGTH(col) - 2) AS col_trimmed
     , /* MySQL Syntax */ SUBSTRING(col,     1,    CHAR_LENGTH(col) - 2) AS col_trimmed
FROM tbl

To remove a specific substring from the end of string, use the TRIM function:

SELECT col
     , TRIM(TRAILING '.php' FROM col)
-- index.php becomes index
-- index.php.php becomes index (!)
-- index.txt remains index.txt

More Related Contents:

  1. How to find similar results and sort by similarity?
  2. SQL order string as number
  3. Can MySQL replace multiple characters?
  4. MySql – Way to update portion of a string?
  5. Recreating a query using INNER JOIN? [duplicate]
  6. Create a Cumulative Sum Column in MySQL
  7. How to perform grouped ranking in MySQL
  8. Equivalent of explode() to work with strings in MySQL
  9. MySQL Trigger after update only if row has changed
  10. MySQL “WITH” clause
  11. mysql select dynamic row values as column names, another column as value
  12. MySQL “Group By” and “Order By”
  13. MySQL DELETE FROM with subquery as condition
  14. MySQL Query to select data from last week?
  15. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  16. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  17. Simple Random Samples from a Sql database
  18. How do I use properly CASE..WHEN in MySQL
  19. Checking multiple columns for one value
  20. What is better in MYSQL count(*) or count(1)?
  21. Delete query not working in mysql
  22. How to insert data to MySQL with auto-incremented column(field)?
  23. Replace Into Query Syntax
  24. How to enable INNODB in mysql
  25. MySQL GROUP BY DateTime +/- 3 seconds
  26. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  27. MySql – How get value in previous row and value in next row? [duplicate]
  28. Updating table in trigger after update on the same table
  29. Syntax error: WITH is not valid input in this position
  30. ORDER BY alphabet first then follow by number

Leave a Comment