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

Accepted Answer

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.

select substring of column returns null and empty

Solution

Problem