MYSQL: multiple functions in query String

Question

the following query is not working in MYSql (a Trigger)

set new.uniq = SUBSTR(md5(concat(new.lat, '-', new.lon)),0,5)

only if i delete the SUBSTR it will give me an correct output

set new.uniq = md5(concat(new.lat, '-', new.lon))

Please, can you put what new.uniq, new.lat and new.lon do? — Francisco Romero
– Francisco Romero, Commented May 31, 2015 at 0:33
If you want the leftmost characters, you can use the LEFT function instead of SUBSTR. For example, if you want the leftmost five characters LEFT(expr,5) — spencer7593
– spencer7593, Commented May 31, 2015 at 1:05

Ed Gibbs · Accepted Answer · 2015-06-14 17:04:59Z

1

The problem is the zero:

set new.uniq = SUBSTR(md5(concat(new.lat, '-', new.lon)),0,5)
                                                         ^

For example:

SELECT SUBSTR('whatever', 0, 5) --> returns empty string
SELECT SUBSTR('whatever', 1, 5) --> returns 'whate'

Change the zero to a 1 and you should be fine:

set new.uniq = SUBSTR(md5(concat(new.lat, '-', new.lon)),1,5)

answered May 31, 2015 at 0:39

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

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

1 Answer 1