mysql query last two parts with dashes

How would you query just the last two parts of this?

what it looks like in the column
myname-is-super
hello-now-why

what I actually need to query out is everything that matches the last two sections
-is-super
-now-why

Any pointers or suggestions is greatly appreciated.

 

Assuming `s` is your field name and `bla` is the table name:

select concat('-',substring_index(s,'-',-2)) x from bla;

PHP:

Here is my test

 

Thank you. I have one more question.

Say if I want to do something like.

update tabe1
inner join tabe2 of table3.info=table2.infosend
where concat ('-',substring_index'-2') table1.match = concat ('-',substring_index'-2') table5.match ??

 

Hi, sorry, but I cannot understand your question.
Which fields to be updated on table1 and table2?
You can use where condition using substring function as you wrote, regular expression too (mysql hasn't preg_replace but in where clause it works as preg_match) or you can update tables one by one-maybe no need to use join or subqueries here.
Please provide tables schema and desired result. If needed you've to explain relations between theese 5 tables-in your example there are table1...table5

 

As an extension of my previous post for better understanding why in some cases regular expression is a must for more precise query, I've extended the example. Please check it and note how results like '--s' and '--y' are skipped from second query.

 

I have 2 columns in table2pull in one table that I want to join together in another column in another table.

Where the numbers before the first dash equal. I have a inner join but I am not sure what I doing wrong

Here is my example

the table I want to update

table1update

id updated_stuff
12-444-22 null
2223-44-1 null
323-222-11 null




table2pull

id junkone junktwo
12-444-22 yippe skippe
2223-44-1 bob bobd
323-222-11 harry pointer




table1update - after its done.

id updated stuff
12-444-22 yippe skippe
2223-44-1 bob bobd
323-222-11 harry pointer


I know how to do the inner join where you match the full number but I don't know how to do the inner join where you match on part of the number. THIS SHOWS FULL NUMBER MATCH.

UPDATE table1update
INNER JOIN table2pull ON transfer_pin_combine.id=table2pull.id
SET table1update.updated_stuff=table2pull.junkone AND table2pull.junktwo


THIS SHOWS WHAT I TRIED WHERE I DID PART OF THE NUMBER QUERY... maybe there is some better way?

UPDATE table1update
INNER JOIN table2pull ON table1update.concat ('',substring_index(s,'-',1))id.table1update =table2pull.concat ('',substring_index(s,'-',1))id.table2pull
SET table1update.updated_stuff=table2pull.junkone AND table2pull.junktwo

 

OK, I think all clear now.
First of all you've to think on your database model.
Next, such NULL values can cause problems with concatenation, but I think I did the query right:

DROP TABLE IF EXISTS table1update,table2pull;

CREATE TABLE table1update(id VARCHAR(255) NOT NULL DEFAULT '',
updated_stuff VARCHAR(255));

INSERT INTO table1update VALUES(
'12-444-22', 'blah'),
('2223-44-1', null),
('323-222-11', null);

CREATE TABLE table2pull(id VARCHAR(255) NOT NULL DEFAULT '',
junkone VARCHAR(255),
junktwo VARCHAR(255));

INSERT INTO table2pull VALUES(
'69-12-444-22', 'yippe', 'skippe'),
('69-2223-44-1', 'bob', 'bobd'),
('101-323-222-11', 'harry', 'pointer');

UPDATE table1update,
(
SELECT id,
GROUP_CONCAT(THE_updated_stuff SEPARATOR ' ') Y FROM (
(SELECT id,
IFNULL(TRIM(updated_stuff),'') THE_updated_stuff FROM table1update
)
UNION ALL
(SELECT SUBSTRING_INDEX(id,'-',-3),
TRIM(
CONCAT(
IFNULL(TRIM(junkone),' '),
' ',
IFNULL(TRIM(junktwo),' ')
)
)
FROM table2pull)
) X
GROUP BY X.id
ORDER BY X.id ASC
) Z
SET table1update.updated_stuff=TRIM(Z.Y)
WHERE table1update.id=Z.id;

Code (markup):

Here is the link
Please note:
-if first table (table1update) contains something <> null, the updated result is concatenated with it-hope this is what you want. If not-you can rebuild the query yourself.
-I'm using substring_index -3 for the example: read inserted data for ID in second table.
Regards: Nick

 

Thank you nick this will surely be a added on bonus to what you have already helped me with