Remove substring from one column in Mysql
Remove substring from one column in Mysql
I want to delete a part of the substring from my table's column
For example, I have a structure like the following:
id | Col1
____________________________________________________________
1 | The string I want and the string I don't
And I need to update my table such that:
id | Col1
____________________________________________________________
1 | The string I want
I tried using the following code:
$var = "UPDATE mytable
SET col1 = replace(col1,col1(SUBSTRING(col1,19),'')";
But this won't work!
1 Answer
1
Try this query to separate address:
UPDATE mytable as a
INNER JOIN
(SELECT
id,
Address1,
LEFT(Address1, 40 -1) as newCol1,
substring(Address1, 40) as newCol2
FROM mytable
where length(Address1) > 40) b
on a.id=b.id
set Address1=newCol1,Address2=newCol2;
if there is any concern about above query please let me know.
I don't need to switch to another column after the comma, I need to switch after a 40 character limit. Intelligent approach though!
– Java_Noob
Jul 1 at 11:18
@Devratna's query would still apply, just substitute your hard limit of 40 for the POSITION functions.
– Sloan Thrasher
Jul 1 at 11:35
"id" is just my auto incrementing primary key, which I specifically did not create in my csv file(As I am converting my csv into MySQL table), So It's throwing an "Unkown column" error.
– Java_Noob
Jul 1 at 12:00
I think you should convert your CSV into MySQL first and then run this query to update fields.
– Devratna
Jul 1 at 12:04
Ok thanks, and incase I have another column called Address3, how can I impose same restrictions on Address2? Will this still work?
– Java_Noob
Jul 1 at 12:22
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Things to research Substring,substring_index,replace
– P.Salmon
Jul 1 at 11:04