-1

I have a list on my database that is like this:

Test,Test2,Test3

I need an way to remove one text of this list without affecting the others. Like this 

Test,Test3

How can i do that? Do I need to explode and then remove it or can I just do that with query on MySQL? OBS: I use MySQL not MySQLi

Cannonball
  • 43
  • 7
  • 2
    Welcome to Stack Overflow! Have you tried writing any code yet? Please review [here](https://www.stackoverflow.com/help/how-to-ask) to learn how to ask a good question. –  Nov 11 '17 at 14:15
  • It's not good? Lol, can you help me to change? – Cannonball Nov 11 '17 at 14:19
  • 1
    Can you clarify what you meant by "a list on my database that is like this:Test,Test2,Test3"? What are the columns and how many rows are we talking about here? – 0xgoku Nov 11 '17 at 14:20
  • @DotComBoy I can't. Stack Overflow is not a free code writing service. –  Nov 11 '17 at 14:21
  • Fix your data structure so you are not storing lists of things in a string. Lists should (generally) be stored as tables in relational databases. – Gordon Linoff Nov 11 '17 at 14:23
  • @Grady it's just 1 line and i use explode to get one player of that list, understand? Srry bad english – Cannonball Nov 11 '17 at 14:23
  • @GordonLinoff I know but i need and prefer to do that list in just one table like this: Blabla;BlaBla2;BlaBla3 and then use explode to get one of that list.. – Cannonball Nov 11 '17 at 14:24
  • @GordonLinoff It's because i have too many players/users registered and i don't want to have Tables and Tables.. – Cannonball Nov 11 '17 at 14:28
  • It's high time you changed your DB design. Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – 1000111 Nov 11 '17 at 14:53
  • 1
    @1000111 I understand, but in my list I do not use numbers with sequences, I just used it here to understand it better, my only concern in putting in just one column the list is that maybe the return of the list with explode may be a bit more time consuming, else if do I create another table with columns it's more faster right? – Cannonball Nov 11 '17 at 14:58

3 Answers3

0

You can use REPLACE function

SELECT REPLACE('Test,Test2,Test3','Test2','');

For query from table;

SELECT REPLACE(YourColumn,'Test2','') from YourTable
lucky
  • 12,734
  • 4
  • 24
  • 46
  • I have tryed this but i don't know why but doesn't work.. I have tryed doing that: $this->db->query("SELECT list FROM database REPLACE('Test,Test2,Test3','Test2','')"); – Cannonball Nov 11 '17 at 14:20
  • Please explain more about the code, so future visitors will not get lost. –  Nov 11 '17 at 14:21
  • It should work that? $this->db->query("SELECT list FROM database REPLACE('Test,Test2,Test3','Test2','')"); – Cannonball Nov 11 '17 at 14:25
  • This would also replace entries like `'Test21', 'OtherTest2'` etc. -- and also leave in a double ',,' comma. Rather replace `(YourColumn, ',Text2,', ',')`. However for that you need to have leading and trailing commas in the list. Lists stored as strings make for ugliness. – Markus AO Nov 11 '17 at 14:49
0

Why don't you do this in 2 steps. The idea is the following:

  1. Get the list, explode, remove the Test 2, then implode

Here, you should already be getting Test,Test3

  1. UPDATE the row of that database with the new String from the implode

Hope that makes sense.

0xgoku
  • 519
  • 2
  • 6
  • 17
0
SELECT 
    REPLACE(your_column, 'Test2', '') AS your_alias
FROM 
    your_table;

or 

SELECT
    REPLACE(your_column, 'Test2', '')
FROM
    your_table;
Vy Do
  • 46,709
  • 59
  • 215
  • 313
  • Hello, i use this: `$a = $this->db->query("SELECT REPLACE('Test1,Test2,Test3', 'Test2', '') AS amigoslista FROM rede_geral WHERE nome='LewBr'"); ` but it don't do nothing here.. it keeps whit Test1,Test2,Test3... – Cannonball Nov 11 '17 at 14:51
  • The true way is `REPLACE(your_column, 'Test2', '')`. Not `REPLACE('Test1,Test2,Test3', 'Test2', '')` – Vy Do Nov 11 '17 at 15:32
  • Nice, but i need to update that... on var_dump i receive that true, but has an way to update and set that on database? – Cannonball Nov 11 '17 at 15:41
  • You can do like this: https://stackoverflow.com/a/37306393/3728901 . **SELECT** data from database, assign result to an array. Get element from arrays of result, put it to **UPDATE** query. – Vy Do Nov 11 '17 at 15:48