0

I have done my research on the subject and I thought about using replace, but the trouble is if I replaced with nothing, then I will end up with lots of spaces in my database field which will get very messy. So I have come to you guys for help.

I have a field in my database called EQUIPMENT. This is an example of what is in one of the fields

15<>5<>6<>3<>2<>1<>14<>13<>12

Each number is representing the ID of a piece of equipment. How would I go about deleting for example <>6 As I said, I looked at using replace but replacing it with ' ' would leave spaces in the field.

Dave Anthony Gordon
  • 35
  • 4
  • use the db REPLACE(column, string_to_find, replace_with) function, but also, please get into the habit of "ALWAYS' using a WHERE clause that uses... WHERE column LIKE(%string_to_find%), so the REPLACE() only happens when string_to_find is actually in column you want to run the REPLACE() on! – Stephanie Temple Mar 04 '19 at 13:04

3 Answers3

0

Replace with '' (no space) should not leave a space. You could also use trim, if you don't want any whitespaces.

Sondre Edvardsen
  • 184
  • 6
0

I m not sure if this is what you want, but before inserting value just use:

echo str_replace("<>", "", "15<>5<>6<>3<>2<>1<>14<>13<>12");

Output: 1556321141312

To delete <>6 use echo str_replace("<>6", "", "15<>5<>6<>3<>2<>1<>14<>13<>12");

Outcome: 15<>5<>3<>2<>1<>14<>13<>12

If you want to do this on mysql database then this could help you: https://stackoverflow.com/a/14586441/5459942

Ingus
  • 943
  • 8
  • 29
0

Instead of replacing a "<>number" by an empty string, you can replace a "<>number<>" by "<>".
This is to avoid that other numbers (that contain that number) are also replaced partially.

And to avoid a wrong result when the number is at the start or the end of the string, a '<>' is concatinated to both ends.

Then those '<>' at both ends can be trimmed after the replace.

Example:

select 
 EQUIPMENT,
 TRIM(BOTH '<>' FROM REPLACE(CONCAT('<>',EQUIPMENT,'<>'),CONCAT('<>',6,'<>'),'<>')) as Equipment_without_6
from
(
 select '15<>5<>6<>3<>2<>1<>14<>13<>12' as EQUIPMENT
 union all select '6<>16<>6<>60<>6'
) q

Returns:

EQUIPMENT                      Equipment_without_6
------------------------------ ---------------------------
15<>5<>6<>3<>2<>1<>14<>13<>12  15<>5<>3<>2<>1<>14<>13<>12
6<>16<>6<>60<>6                16<>60
LukStorms
  • 19,080
  • 3
  • 26
  • 39
  • I got past that issue kind of but now my issue has moved on to soomething else. This is what is in the field I am changing 15<>5<>6<>3<>2<>1<>14<>13<>12 – Dave Anthony Gordon Mar 05 '19 at 20:22
  • mysql_query(UPDATE Drug_Factory SET EQUIPMENT= REPLACE(EQUIPMENT, '<>1', '') WHERE ID='$factory_2_id' ") or die(" connect to database failled!"); This is the code I am using, it is deleting the <>1 on it is own but it is also deleting the 1 in the <>14 leaving it as <>4 is there any way to only replace exact matches – Dave Anthony Gordon Mar 05 '19 at 20:23
  • If you take a closer look at the answer you'll notice it replaces not '<>6' by '', but '<>6<>' by '<>'. It's to avoid stuff like also changing other numbers that have a 6. – LukStorms Mar 05 '19 at 20:48