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

Leafgreen · #1 (**permalink**) 07-01-11, 15:01

I have a table with cells* that contain the following text

Quote:

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

Quote:

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

mike_bike_kite · #2 (**permalink**) 07-01-11, 15:13

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.

mike_bike_kite · #3 (**permalink**) 07-02-11, 05:13

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.

Leafgreen · #4 (**permalink**) 07-02-11, 16:25

Thanks for your persistence with me, Mike.

Quote:

Originally Posted by mike_bike_kite

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.

mike_bike_kite · #5 (**permalink**) 07-02-11, 17:19

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;

Leafgreen · #6 (**permalink**) 07-02-11, 17:27

I should have explained that the table contains hundreds of rows, and the variable text is variable in each row.

mike_bike_kite · #7 (**permalink**) 07-02-11, 17:38

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?

Leafgreen · #8 (**permalink**) 07-02-11, 17:49

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?

mike_bike_kite · #9 (**permalink**) 07-02-11, 18:12

Quote:

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.

Leafgreen · #10 (**permalink**) 07-02-11, 18:15

Yes, the random characters do contain spaces, as in the example 1234 xyz and Dbforums is awesome.

mike_bike_kite · #11 (**permalink**) 07-03-11, 04:22

So how do you tell where the bit you want to keep ends?

Leafgreen · #12 (**permalink**) 07-03-11, 05:04

Quote:

Originally Posted by mike_bike_kite

the bit you want to keep

To which parts are you referring in the example?

Ikviens · #13 (**permalink**) 07-03-11, 05:46

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.

Leafgreen · #14 (**permalink**) 07-05-11, 18:19

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.

Ikviens · #15 (**permalink**) 07-05-11, 21:04

Use a condition like below in your WHERE clause:
LOCATE('ssssss', colA)*LOCATE('tttttt', colA) > 0

Internet Services

Web Development

Digital Marketing

Consumer Tech