MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (name VARCHAR(50) NOT NULL PRIMARY KEY);
INSERT INTO tbl VALUES
  ('David'),
  ('David James Malan'),
  ('David James Malan Neeko'),
  ('David James Malan Mathew');

Records: 4  Duplicates: 0  Warnings: 0

SET @name_str = 'David';

SELECT *
FROM tbl
WHERE name LIKE CONCAT(@name_str, '%') OR @name_str LIKE CONCAT(name, '%')
ORDER BY name = @name_str DESC, name LIKE CONCAT(@name_str, '%') DESC;

name

David

David James Malan

David James Malan Mathew

David James Malan Neeko

SET @name_str = 'David James Malan';

SELECT *
FROM tbl
WHERE name LIKE CONCAT(@name_str, '%') OR @name_str LIKE CONCAT(name, '%')
ORDER BY name = @name_str DESC, name LIKE CONCAT(@name_str, '%') DESC;

name

David James Malan

David James Malan Mathew

David James Malan Neeko

David

SET @name_str = 'David James Malan Neeko';

SELECT *
FROM tbl
WHERE name LIKE CONCAT(@name_str, '%') OR @name_str LIKE CONCAT(name, '%')
ORDER BY name = @name_str DESC, name LIKE CONCAT(@name_str, '%') DESC;

name

David James Malan Neeko

David

David James Malan

SET @name_str = 'David jam Mlan';

SELECT *
FROM tbl
WHERE name LIKE CONCAT(@name_str, '%') OR @name_str LIKE CONCAT(name, '%')
ORDER BY name = @name_str DESC, name LIKE CONCAT(@name_str, '%') DESC;

name

David

SET @name_str = 'David James';

SELECT *
FROM tbl
WHERE name LIKE CONCAT(@name_str, '%') OR @name_str LIKE CONCAT(name, '%')
ORDER BY name = @name_str DESC, name LIKE CONCAT(@name_str, '%') DESC;

name

David James Malan

David James Malan Mathew

David James Malan Neeko

David