SELECT SUBSTRING_INDEX("www.w3schools.com", "sc", 1);The code above produces the result below.
I like to get the rest part of it.
The would-be code below does’t work correctly but I hope it shows what I want.
would-be code
SELECT theLastPart_SUBSTRING_INDEX("www.w3schools.com", "sc", 1);would-be result
SELECT SUBSTRING_INDEX("www.w3schools.com", "3", -1);
SELECT SUBSTRING_INDEX("www.w3schools.com", "sc", -1);
SELECT SUBSTRING_INDEX("Germany is in Europe and Berlin is its capital", "p", 1);The SQL above produces the result below.
I like to get the rest part of it.
The would-be code below does’t work correctly but I hope it shows what I want.
would-be code
SELECT theRestPart_SUBSTRING_INDEX("Germany is in Europe and Berlin is its capital", "p", 1);target result
Can I get my target result in SQL with your help?
Or I should do it in PHP?
By the way the code below produces the result below which is NOT what I want.
SELECT SUBSTRING_INDEX("Germany is in Europe and Berlin is its capital", "p", -1);please allow me to introduce you to da manual – MySQL string functions
use LOCATE() to find the position of the first 'p'
then use SUBSTRING() to pull out the rest of the string after that
it’s easier if you nest them –
SELECT SUBSTRING('Germany...' FROM LOCATE('p','Germany...')+1)I am afraid that I don’t know how to apply your code above.
$key='p'; $like="%$key%";
$sql="SELECT contents
FROM myTable
WHERE contents like ?";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like]);The code above is the original code of mine.
The code below is one of my trials for applying your code which produces Fatal Error
$key='p'; $like="%$key%";
$sql="SELECT contents
FROM LOCATE($key, contents)+1) FROM myTable
WHERE contents like ?";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like]);The trial below also produces Fatal error.
$sql="SELECT LOCATE($key, contents)+1) as afterKey FROM myTable
WHERE contents like ?";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like]);you must keep trying
compare what i wrote –
SELECT SUBSTRING('Germany...' FROM LOCATE('p','Germany...')+1)to what you wrote –
SELECT contents FROM LOCATE('p','Germany...')+1)there’s a difference, yes?
did you actually try using what i wrote?
Where should I put the table name “myTable”?
The following is another trails of mine for it.
SELECT SUBSTRING(contents FROM LOCATE($key, contents)+1) FROM myTable) as afterKey FROM myTablein the FROM clause of the query
what i gave you is an expression that goes in the SELECT clause
expressions in the SELECT clause can be column names –
SELECT column1
, column2
, buckle_my_shoe
FROM ...or they can be calculations on columns –
SELECT curly * 3
, larry + 2
, moe / 1
FROM ...or they can be functions –
SELECT AVG(iq)
, AVG(hatsize)
FROM ...and in this case, the expression is a SUBSTRING function with an embedded LOCATE function –
SELECT SUBSTRING( ... FROM LOCATE(...) ...)
FROM ...The code below produces fatal error : Uncaught PDOException:
$sql="SELECT SUBSTRING(contents FROM LOCATE($key, contents FROM myTable) WHERE contents like ?) as afterKey FROM myTable
WHERE contents like ?";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like]);You can’t use WHERE in SUBSTRING(... FROM LOCATE()).
The FROM keyword here is NOT the same as the FROM part of the entire query. It’s part of SUBSTRING(string FROM x FOR y).
As an alternative you can also use commas SUBSTRING(string, x, y)
That would make Rudy’s earlier suggestion: SUBSTRING('Germany...', LOCATE('p','Germany...')+1)
Does that help at all?
$key='p'; $like="%$key%";
$sql="SELECT SUBSTRING(contents, LOCATE($key, contents), 5) as afterKey FROM myTable
WHERE contents like ?";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like]);The code above produces the error below.
you must include the single quotes
you want this –
LOCATE('p',...)not this –
LOCATE(p,...)and where did the 5 come from? why do you want only 5 characters returned?
i thought you said you wanted the rest of the string?
wait, i think i know where the 5 came from
you borrowed some code you found in @rpkamp’s reply, which had 3 parameters for the SUBSTRING function
no, man, you need to go back and look at what i originally gave you –
SUBSTRING('Germany...' FROM LOCATE('p','Germany...')+1)notice the FROM?
you should go to the link to the manual i gave you, to understand what the FROM option without the FOR option does
and you also should think about why i used +1
“+1” was NOT important because I was focus on removing the error.
As I include the single quotes, I can remove the error.
Now I add “+1” like the following.
SELECT SUBSTRING(contents, LOCATE('$key', contents)+1, 5) as afterKey FROM myTableThe code produces the my target result below.
The code below does work fine.
SELECT id, right(SUBSTRING_INDEX(contents, '$key', 1), 5) as beforeKey, SUBSTRING(contents, LOCATE('$key', contents)+1, 5) as afterKey FROM myTableThe result above produced by PHP code below.
$searchL['beforeKey'].<span style="color:red">' .$key. '</span>' .$searchL['afterKey'].SQL gives 3 strings, the 1st one is $beforeKey, the 2nd one is $key, and the 3rd one is afterKey.
I am thinking another question.
Can I get, I am thingking, 1 string which has 5 characters beforeKey, $key, and 5 characters afterKey as a whole from SQL ?
sure you can
go back to the manual on string functions and look for the one called CONCAT
SELECT
CONCAT(right(SUBSTRING_INDEX(contents, '$key', 1), 5), '$key', SUBSTRING(contents, LOCATE('$key', contents)+1, 5) ) as concatString
FROM myTableThe code above works fine.
Thank you.
(id) title contents
(1) Asia This is a continent
(2) China This country is big in population
(3) France This country is in Europe and Paris is its capital
(4) Mongolia Mongol is country
(5) Korea Korea
(6) Germany Germany is in Europe and Berlin is its capitalI have myTable like the above.
I made the code below.
$key1='a'; $key2='p'; $like1="%$key1%"; $like2="%$key2%";
$sql="SELECT id, title,
CONCAT(right(SUBSTRING_INDEX(contents, '$key1', 1), 5), '$key1', SUBSTRING(contents, LOCATE('$key1', contents)+1, 5) ) as key1String,
CONCAT(right(SUBSTRING_INDEX(contents, '$key1', 1), 5), '$key1', SUBSTRING(contents, LOCATE('$key1', contents)+1, 5) ) as key1String,ONCAT(right(SUBSTRING_INDEX(contents, '$key1', 1), 5), '$key1', SUBSTRING(contents, LOCATE('$key1', contents)+1, 5) ) as key1String,
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.
The result above is fine.
This time I like to get one string only which comes first between key1String and key2String.
my target result is like the following.
I can do, I think, it in PHP.
I like to do it in SQL
you’ve made errors in copying from your computer to this forum
ONCAT is a typo
key2String is missing
there is a floating comma in front of FROM
you must try to be more rigorous in checking your own work
The followig is, I am sorry, the modified one rigorously .
$key1='a'; $key2='p'; $like1="%$key1%"; $like2="%$key2%";
$sql="SELECT id, title,
CONCAT(right(SUBSTRING_INDEX(contents, '$key1', 1), 5), '$key1', SUBSTRING(contents, LOCATE('$key1', contents)+1, 5) ) as key1String,
CONCAT(right(SUBSTRING_INDEX(contents, '$key2', 1), 5), '$key2', SUBSTRING(contents, LOCATE('$key2', contents)+1, 5) ) as key2String
FROM myTable
WHERE contents like ? AND contents like ? ORDER BY id";
$searchQ=$dbc-> prepare ($sql);
$searchQ->execute([$like1, $like2]);so content has to contain both $key1 and $key2 because of the LIKEs
then $key1String and $key2String concatenate the 5 characters to the left and 5 characters to the right of both $key1 and $key2
what happens if $key1 or $key2 is more than one character? because then the 5 characters following, where you have LOCATE()+1, will actually be the first 5 characters after the first character of $key1 or $key2
or what happens if $key1 or $key2 occurs closer than 5 characters to the beginning or the end of content? do the SUBSTRINGs work in that situation?
once you have successfully tested your string formulae to cover all those situations, then we can move on to your real question –
my understanding is, you want to pick between $key1String and $key2String and pick the one that occurs first when reading from left to right
if this is correct, you can use the LOCATE function in a CASE expression
CASE WHEN LOCATE(... $key1 ...) > LOCATE(... $key2 ...)
THEN something something key1String
ELSE something something key2String END