1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

mysql query last two parts with dashes

Discussion in 'MySQL' started by xbat, Jan 16, 2014.

  1. #1
    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.
     
    Solved! View solution.
    xbat, Jan 16, 2014 IP
  2. #2
    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
     
    koko5, Jan 19, 2014 IP
  3. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #3
    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 ??
     
    Last edited: Feb 13, 2014
    xbat, Feb 13, 2014 IP
  4. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #4
    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
     
    koko5, Feb 14, 2014 IP
  5. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #5
    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.
     
    koko5, Feb 14, 2014 IP
  6. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #6
    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
     
    xbat, Feb 14, 2014 IP
  7. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #7
    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 :)
     
    koko5, Feb 14, 2014 IP
    ryan_uk likes this.
  8. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #8
    Thank you nick this will surely be a added on bonus to what you have already helped me with :)
     
    xbat, Feb 14, 2014 IP