0

I have got a string like this

****1****2****22****111****3****34****21****33****23****213****213****222****55****152****36

Let's say I have a new number to add to this string like ****44,

I want to UPDATE the string and add that number ****44 Only if it is not already in there.

If a number is already there i.e. Suppose I want to add ****22, the UPDATE should not happen because ****22 is already in the string.....

However, if I have ****22222, the UPDATE should Proceed.

How can this possibly be done:

So far I have this great thoughts from this SO Link: MySQL string replace

However, using this code (got from the SO Link as the Accepted answer there):

UPDATE your_table
SET your_field = REPLACE(your_field, 'articles/updates/', 'articles/news/')
WHERE your_field LIKE '%articles/updates/%'

I dont get how the checking of if the piece already exists will be done cause, this seems to be a direct change from one string to another.

Any Suggestion is honored.

Community
  • 1
  • 1
Universal Grasp
  • 1,835
  • 3
  • 20
  • 29
  • Where in the string do you want to add the new piece of data? The query in your post replaces existing values - it doesn't add new. – jpw Oct 31 '14 at 17:12
  • The new `piece` can be added anywhere in the string... Thank you! – Universal Grasp Oct 31 '14 at 17:13
  • I'm curious, why are you storing IDs like this? – Jorge Campos Oct 31 '14 at 17:17
  • These `Ids` are `Questions` of Give `Subjects`. Each `Subject` has like 30`Questions`. The `questions` are stored in that manner to be dealt with as an `Array` when we `Split(****)` for each `Subjects`. So suppose I had 20 `Subjects` and 30`Qstn/Subjcts` We avoided having a new *(third)* `Table` for their relationship. It might no be a good practice but Works Faster so far. – Universal Grasp Oct 31 '14 at 17:23
  • yes, it's not a good pratice. :) – Jorge Campos Oct 31 '14 at 17:33

2 Answers2

1

Assuming the column you want to add data to is of a character type this should work:

update your_table
set your_column = concat(your_column, '****22')
where your_column not like '%****22*%' 
  and your_column not like '%****22';

The first where condition filters out cases where****22would match a longer similar sequence like****222in the middle of the string by checking that the match is followed by a*and the second condition filters out matching rows that end with****22and thus are not followed by any*.

This will append****22at the end ofyour_column

Sample SQL Fiddle

jpw
  • 44,361
  • 6
  • 66
  • 86
0

The following statement will update all rows and tack on a '22' to the end of 'myval' if it doesn't have '22' in the value somewhere. If you're literally dealing with *** characters, you have to escape them in the regexp.

update foo set myval= if(myval regexp '22',myval,concat(myval,'22'));
TX Turner
  • 157
  • 9
  • Thanx though i get ERROR: `#1139 - Got error 'repetition-operator operand invalid' from regexp` – Universal Grasp Oct 31 '14 at 17:28
  • Yeah, you have to escape the *** characters. That's why my example doesn't use em. I actually ran this query on a temp table- the first time, it tacks on '22' to all rows that don't already have '22', the second time, it affects no rows, because it already updated them. – TX Turner Oct 31 '14 at 17:29
  • What I wrote: `UPDATE `all_sets_subjects` SET `all_questions_ids` = if(`all_questions_ids` `regexp` `'****22'`,`all_questions_ids`,`concat(myval,'22')``);` – Universal Grasp Oct 31 '14 at 17:30
  • Give this a shot-- UPDATE all_sets_subjects` SET all_questions_ids = if(all_questions_ids regexp '\\*\\*\\*\\*22',all_questions_ids,concat(myval,'****22')``) -- err... SO seems to be munging slashes.. there should be two slashes per asterisk in the regexp. – TX Turner Oct 31 '14 at 17:44