47

I have a table in a MySQL database that I am running simple SELECT queries on (for quick diagnostics/analysis - since I am not running phpmyadmin on the server - for security reasons).

I would like to be able to truncate the returned data using something like this:

select id, LEFT(full_name, 32), age FROM user

where user is a table that contains the columns id, full_name and age

I tried the above statement and it didn't work. anyone knows how to do this?

[Edit]

Sorry, when I said it dosen't work, I mean mySQL simply returns the STRING "LEFT(full_name, 32)" as an alias for the column 'full_name' and outputs the field value - which in this case, can be as long as 256 chars.

Improve this question
2
  • 2
    In what way doesn't it work? What result are you getting, and what do you expect to get? Do you need to set an alias select id, LEFT(full_name, 32) as full_name, age FROM user to acces sit in your code?
    – Mark Baker
    Commented Jul 20, 2010 at 13:55
  • In my testing, the code shown above works as-is in current mysql. I don't know if this was a bug at the time question was asked.
    – ToolmakerSteve
    Commented Feb 22, 2019 at 2:50

3 Answers 3

Reset to default
71 
select id, SUBSTRING(full_name,1, 32), age FROM user

Quoting mysql.com:

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

Improve this answer
4
  • 1
    As pointed out by Mark Baker in the comments above, an alias on the SUBSTRING would be a good idea
    – Brendan Bullen
    Commented Jul 20, 2010 at 14:10
  • 1
    more details about SUBSTRING here : 1keydata.com/sql/sql-substring.html
    – Julien
    Commented Mar 7, 2013 at 16:24
  • 1
    How is this any different than what OP did with Left? ... SUBSTRING(full_name, 1, 32), ... is equivalent to ... Left(full_name, 32), .... Or at the time this answer was given, was mysql Left buggy? Anyway, OP's original code works for me.
    – ToolmakerSteve
    Commented Feb 22, 2019 at 2:46
  • 1
    It was 9 years ago, maybe left() did not exist yet
    – mvds
    Commented Feb 22, 2019 at 22:43
6 
select id, SUBSTR(full_name, 1, 32), age FROM user;

OR

select id, SUBSTRING(full_name, 1, 32), age FROM user;

OR

select id, SUBSTRING(full_name, FROM 1 FOR 32), age FROM user

Note: SUBSTR() is a synonym for SUBSTRING().

found on mysql doc

Improve this answer
4 
select id, SUBSTRING(full_name,1, 32), age FROM user
Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.