Why Left() returns to 0, but substring () not

Question

A Practice Q from HackerRank. Very confused about the difference between left() and substring () in the case: Reference: https://www.hackerrank.com/challenges/the-pads/problem Considering the following, it is returned to Name(0)

SELECT CONCAT (NAME,'(',LEFT('Occupation',1),')') 
   FROM OCCUPATIONS 
   ORDER BY NAME

but the following is correct

SELECT CONCAT (NAME,'(',substring('Occupation',1,1),')') 
   FROM OCCUPATIONS 
   ORDER BY NAME

Both queries work fine if you remove the single quotes around `Occupation`. They shouldn't be there as you want `Occupation` to be a column name, not a string. https://www.db-fiddle.com/f/rdav2qu5CgHhhbinZxvpA4/0 — Nick, Apr 30 '20 at 01:41

score 1 · Accepted Answer · answered Apr 30 '20 at 01:36

Neither is correct. 'Occupation' (with single quotes) is the string that starts with the letter O, which is what both of your examples should yield.

You want "OCCUPATION" (with double quotes) or more commonly OCCUPATION (with no quotes) instead, which is a column name.

LEFT(..., 1) and SUBSTRING(..., 1, 1) are equivalent; your error is elsewhere.

Why Left() returns to 0, but substring () not

1 Answers1