The rest part of SUBSTRING_IDEX

Databases
21 
$key1='a'; $key2='p'; $like1="%$key1%"; $like2="%$key2%";
$sql="SELECT id, title,
CASE WHEN LOCATE('$key1', contents) > LOCATE('$key2', contents)
THEN CONCAT (right(SUBSTRING_INDEX(contents, '$key2', 1), 5), 
'$key2', SUBSTRING(contents, LOCATE('$key2', contents)+1, 5) ) as keyString
ELSE CONCAT( right(SUBSTRING_INDEX(contents, '$key1', 1), 5), 
'$key1', SUBSTRING(contents, LOCATE('$key1', contents)+1, 5) ) as keyString END
FROM myTable
WHERE contents like ? AND contents like ? ORDER BY id";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like1, $like2]);

The code above produces the Fatal error below.

22

The code above turns correctly

23

The code below works fine.

$key1='co'; $key2='a'; $key1Len=strLen($key1Len); $key2Len=strLen($key2Len); $like1="%$key1%"; $like2="%$key2%";
$sql="SELECT id, title,
CASE WHEN LOCATE('$key1', contents) > LOCATE('$key2', contents)
THEN CONCAT (right(SUBSTRING_INDEX(contents, '$key2', 1), 5), 
'$key2', SUBSTRING(contents, LOCATE('$key2', contents)+$key2Len, 5) ) as keyString
ELSE CONCAT( right(SUBSTRING_INDEX(contents, '$key1', 1), 5), 
'$key1', SUBSTRING(contents, LOCATE('$key1', contents)+$key1Len, 5) ) as keyString END
FROM myTable
WHERE contents like ? AND contents like ? ORDER BY id";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like1, $like2]);

The code above produces the result below.

Do you see any flaw with the code above?

24

no it doesn’t

yes

you cannot assign a column alias inside a CASE expression

1 Like
25 
$key1='co'; $key2='a'; $key1Len=strLen($key1Len); $key2Len=strLen($key2Len); $like1="%$key1%"; $like2="%$key2%";
$sql="SELECT id, title,
CASE WHEN LOCATE('$key1', contents) > LOCATE('$key2', contents)
THEN CONCAT (right(SUBSTRING_INDEX(contents, '$key2', 1), 5), 
'$key2', SUBSTRING(contents, LOCATE('$key2', contents)+$key2Len, 5) ) 
ELSE CONCAT( right(SUBSTRING_INDEX(contents, '$key1', 1), 5), 
'$key1', SUBSTRING(contents, LOCATE('$key1', contents)+$key1Len, 5) )  END as keyString
FROM myTable
WHERE contents like ? AND contents like ? ORDER BY id";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like1, $like2]);
26

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.