Replace a character in multiple mysql database column

Question

Hello I am new to mySQL and i am having trouble trying extracting data from my db by using keywords. I have 2 columns (tags and categories). I have done this before using the LIKE '%keyword%' in my query. The problem is in the db i am using now each keyword is separated with a ';' i would like to know if it is possible to somehow separate each keyword by replacing the semi-colon with a space? Or if it's easier use the same query but instead of using '%' to separate i use semi-colons? sorry if its a newbie question but i am struggling hard with this and really need some help.

Thanks

M.mhr · Accepted Answer · 2016-05-05 22:46:12Z

1

You can replace semi-colon with space ( or any other ) with REPLACE function :

UPDATE MyTable SET tags = REPLACE(tags,';',' '), categories = REPLACE(categories,';',' ')

answered May 5, 2016 at 22:46

M.mhr

1,7592 gold badges24 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Valeriy Solovyov · Accepted Answer · 2016-05-05 22:34:57Z

The problem is in the db i am using now each keyword is separated with a ';' i would like to know if it is possible to somehow separate each keyword by replacing the semi-colon with a space? Or if it's easier use the same query but instead of using '%' to separate i use semi-colons?

Example table:

CREATE TABLE `test` (  
  `tags` varchar(255) NOT NULL,
  `categories`  varchar(255) NOT NULL  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Sample data:

insert into `test` values ('tag', 'cat' );
insert into `test` values ('tag', 'cat1;cat2;cat3;' );

Example select:

select * from `test` where `categories` LIKE '%cat1;%';
+------+-----------------+
| tags | categories      |
+------+-----------------+
| tag  | cat1;cat2;cat3; |
+------+-----------------+
1 row in set (0.00 sec)

Collectives™ on Stack Overflow

Replace a character in multiple mysql database column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related