Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2008
    Posts
    36

    Exclamation Unanswered: this query might be a challenge. Can you do it?

    I have a table with cells* that contain the following text
    vvvvv ssssss yyyyy tttttt
    where vvvvv and yyyyy always have variable values, but not variable in position in relation to sssss and tttttt. The sssss and tttttt values are always the same/static.

    I want to move
    ssssss yyyyy tttttt
    (not including vvvvv variable text) to append to the end of another column ("columnB") with the same rows and table. Can you reply with the query, please?

    *I use the term "cell" to mean the intersection of one row and one column in a table. Hope that's correct.

    Thank you,
    Leafgreen
    Last edited by Leafgreen; 07-01-11 at 16:35. Reason: re-written to simplify in response to @mike's post

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Understanding your query is certainly a challenge - I read your question twice and still can't work out what you want. You'll get a better standard of response if you just post a few examples of what you need.

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You've changed your original query but it really isn't any clearer to me. Does the string you want moving always start in exactly the same position or is it after a certain pattern is found in the original string? If you post a few examples then this will be quite clear and I can help you. If you can't then I suggest you go to the manual page on MySQL string functions and look up instr and substr.

  4. #4
    Join Date
    Apr 2008
    Posts
    36
    Thanks for your persistence with me, Mike.
    Quote Originally Posted by mike_bike_kite View Post
    Does the string you want moving always start in exactly the same position or is it after a certain pattern is found in the original string?
    Yes, ssssss always follows vvvvv. But, vvvvv always has variable values.

    Examples:
    Cell value of ColumnA, row1: vvvvv ssssss yyyyy tttttt
    Cell value of ColumnA, row2: 1234 xyz ssssss Dbforums is awesome.tttttt

    Cell value of ColumnB, row1: uuuuuuu
    Cell value of ColumnB, row2: append tothe endofthis

    Run query. Result:
    Cell value of ColumnA, row1: vvvvv
    Cell value of ColumnA, row2: 1234 xyz

    Cell value of ColumnB, row1: uuuuuuu ssssss yyyyy tttttt
    Cell value of ColumnB, row2: append tothe endofthis ssssss Dbforums is awesome.tttttt

    So, note that ssssss and tttttt remain unchanged before and after the query in both rows. They and the second set of variable text no longer remain in ColumnA, row1 and row2. And the designated values are moved to ColumnB, row1 and row2.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The important bit is the 2 updates:
    Code:
    create table tmp(
            colA varchar(255),
            colB varchar(255)
    );
    
    insert tmp values ( "vvvvv ssssss yyyyy tttttt","uuuuuuu" );
    insert tmp values ( "1234 xyz ssssss Dbforums is awesome.tttttt","uuuuuuu" );
    
    update tmp
    set colB = concat( colB, substr( colA, instr(colA," ") ) );
    
    update tmp
    set colA = substr( colA, 1, instr(colA," ") );
    
    select * from tmp;
    
    drop table tmp;

  6. #6
    Join Date
    Apr 2008
    Posts
    36
    I should have explained that the table contains hundreds of rows, and the variable text is variable in each row.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    My solution works for all the examples you supplied.
    It should work whether there is 1 row or millions.

    Please supply examples of where it didn't work or would you like to restart the game where you half specify the problem and we all have to guess what the issue is?

  8. #8
    Join Date
    Apr 2008
    Posts
    36
    I'm not playing a game here. Please respect that this a matter of us understanding each other in good faith...

    I'm trying to explain that the variable values are different in every row. Let's put it this way: In the place of vvvvv and yyyyy are random characters. Will your query work if those values are random in each row?

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Will your query work if those values are random in each row?
    Yes of course - assuming the random characters don't contain spaces. I suggest you copy the tables you're doing the work on. Then run the supplied updates on the new tables and see if everything is fine. Then run the updates on the main table.

  10. #10
    Join Date
    Apr 2008
    Posts
    36
    Yes, the random characters do contain spaces, as in the example 1234 xyz and Dbforums is awesome.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    So how do you tell where the bit you want to keep ends?

  12. #12
    Join Date
    Apr 2008
    Posts
    36
    Quote Originally Posted by mike_bike_kite View Post
    the bit you want to keep
    To which parts are you referring in the example?

  13. #13
    Join Date
    Mar 2006
    Posts
    56
    Assuming two things, I find the string operation easy. First, ssssss occurs only once in the field. Second, tttttt marks the end of the field. In other words, there is no more than one ssssss or tttttt. When you find tttttt in the field, that's the end of the field.

    To begin with, you should find out what part you want to keep intact from the older field:
    Code:
    SUBSTR(colA, LOCATE('ssssss', colA))
    Then, prefix whatever you like:
    Code:
    SELECT colA, CONCAT('uuuuuuu ', SUBSTR(colA, LOCATE('ssssss', colA)))from tmp;
    The first argument of CONCAT can be a literal or a field name.

  14. #14
    Join Date
    Apr 2008
    Posts
    36
    No, ssssss occurs only in some rows in ColumnA. Likewise, tttttt occurs and marks the end of only some rows in ColumnA. I only want the query to be activated in those rows where ssssss and tttttt occur.

  15. #15
    Join Date
    Mar 2006
    Posts
    56
    Use a condition like below in your WHERE clause:
    LOCATE('ssssss', colA)*LOCATE('tttttt', colA) > 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •