Is there any way to insert string in MySQL?

Question

I have a table Template. In that I have one column like Message.For example

-------------------------------
id  name  desc cretaedBy  Message
-------------------------------
1    A     blah  today      <td><tr>one</tr></td></body></html>
2    B     blah  lastday
3    C     blah  onemonth   <tr><td>last</td><td>next</td></tr></body></html>

Here, i need to insert <div>blah blah some content</div> before </body></html> in Message-column.

How to find the position and update in particular position using update query.

This table shows 3records for example only..but it may contain1000records. Actually what i am expecting, for example if java or any programming language...if string contains end body tag , substring the string and add data before end body tag.

Anyone please help me in mysql

So you want to insert it into the same column? (row 3 column `Message`) — Willem, Jan 03 '20 at 17:24
you would need to update the whole column for the specific row. Construct the new value for the column and then update — uneq95, Jan 03 '20 at 17:32
Does this answer your question? [Store HTML into MySQL database](https://stackoverflow.com/questions/2641561/store-html-into-mysql-database) — Shekhar Rai, Jan 03 '20 at 17:47
@shekhar-no..i want to execute if footer contains end body tag
, i want to insert some content before — Learner, Jan 03 '20 at 19:03

score 0 · Answer 1 · answered Jan 03 '20 at 17:30

0

You can use an update statement:

UPDATE Template SET Message = '<tr><td>last</td><td>next</td></tr><div>blah blah some content</div></body></html>' 
WHERE Message = '<tr><td>last</td><td>next</td></tr></body></html>';

answered Jan 03 '20 at 17:30

Willem

992
6
13

No. you are using constant value in where condition.Some cases ,records wont contain no data in Message . i am asking if footer contains
, I have to insert 'data' before
– Learner Jan 03 '20 at 17:35
Please put that information into your question. So you have to insert data before
but sometimes it's empty so what do you want then?
– Willem Jan 03 '20 at 17:38
Hi willem thanks.i have updated my question . 1000 records already there...if message column contains
, i need to add some data before ...so if some records contains no data in message column , i should not update corresponding the record..
– Learner Jan 03 '20 at 19:31

o_nerti · Answer 2 · 2020-01-03T18:07:22.523

Try this: Create template DDL statement:

CREATE TABLE `mytable` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) unsigned DEFAULT NULL COMMENT 'a name comment',
  `Message` varchar(5000) DEFAULT NULL COMMENT 'an address comment',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`))

Create template INSERT data statement:

Insert into mytable (name,message) VALUES('A','<td><tr>one</tr></td>?</body></html>')
...
Insert into mytable (name,message) VALUES('C','<td><tr>last</tr></td>?</body></html>')

Use Prepare. Your data will be inserted into the SQL query template at the question mark place.

    mysql> PREPARE stmt1 FROM 'Insert into mytable (name,message) VALUES('A','<td><tr>one</tr></td>?</body></html>');
Insert into mytable (name,message) VALUES('C','<td><tr>last</tr></td>?</body></html>');';
mysql> SET @a = '<div>blah blah some content</div>';
mysql> EXECUTE stmt1 USING @a;
mysql> DEALLOCATE PREPARE stmt1;

Create and fill in the table with the data whenever necessary.

Hi thanks.but i need to write update script...bcoz already records there..i want to update wherever message column contains — Learner, Jan 03 '20 at 19:27
then export the whole table along with its contents to a sql file and perform the replacement in a text editor. Then drop your table and run the script. — o_nerti, Jan 03 '20 at 22:43

score 0 · Accepted Answer · answered Jan 04 '20 at 05:19

You can use an UPDATE with a REPLACE, where you replace </body></html> with <div>blah blah some content</div></body></html>. In Message values which don't have </body></html> in them, nothing will happen:

UPDATE Template
SET Message = REPLACE(Message, '</body></html>', 'div>blah blah some content</div></body></html>')
;
SELECT * FROM Template

Output:

id  name    desc    createdBy   Message
1   A       blah    today       <td><tr>one</tr></td>div>blah blah some content</div></body></html>
2   B       blah    lastday     (null)
3   C       blah    onemonth    <tr><td>last</td><td>next</td></tr>div>blah blah some content</div></body></html>

Demo on SQLFiddle

If your table is very big, qualifying the UPDATE with WHERE Message LIKE '%</body></html>' will speed it up.

Is there any way to insert string in MySQL?

3 Answers3