select substring of column returns null and empty

Question

I'm having trouble using mysql's substr function.

my query is:

SELECT distinct(substr(col1,0,10)) from table;

The results returned are NULL and an empty row.

Am I using substr incorrectly, or can I not use distinct or a column name?

thanks

Ja͢ck · Accepted Answer · 2013-08-13 02:41:15Z

7

Firstly, the position of the first character of a string is 1, not 0; this should fix it:

SELECT distinct(substr(col1, 1, 10)) 
FROM `table`

Secondly, your table contains at least one row in which col1 is NULL. For those rows, the result of SUBSTR is NULL as well.

answered Aug 13, 2013 at 2:41

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thanks jack! i missed that sentence on position=1, but it's clearly in the documentation. you saved the day again!