Update column from Base64 to string (natively in SQL)
I've been trying to figure this out for two days now and would really appreciate some help. I've imported data from a csv where one field contained html data encoded in base64.
The idea is to loop over every row and run FROM_BASE64 on it.
How do I structure a query that:
- Loops over all lines
- Calls FRON_BASE64 for each line
- Runs UPDATE (or similar functionality) on that same row and column
Context: I'm running MariaDB (MySQL equivalent).
Thanks for any help!
mysql sql mariadb
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
asked Nov 19 '18 at 18:11
MentorMentor
472213
add a comment |
I've been trying to figure this out for two days now and would really appreciate some help. I've imported data from a csv where one field contained html data encoded in base64.
The idea is to loop over every row and run FROM_BASE64 on it.
How do I structure a query that:
- Loops over all lines
- Calls FRON_BASE64 for each line
- Runs UPDATE (or similar functionality) on that same row and column
Context: I'm running MariaDB (MySQL equivalent).
Thanks for any help!
mysql sql mariadb
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
asked Nov 19 '18 at 18:11
MentorMentor
472213
add a comment |
I've been trying to figure this out for two days now and would really appreciate some help. I've imported data from a csv where one field contained html data encoded in base64.
The idea is to loop over every row and run FROM_BASE64 on it.
How do I structure a query that:
- Loops over all lines
- Calls FRON_BASE64 for each line
- Runs UPDATE (or similar functionality) on that same row and column
Context: I'm running MariaDB (MySQL equivalent).
Thanks for any help!
mysql sql mariadb
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
asked Nov 19 '18 at 18:11
MentorMentor
472213
I've been trying to figure this out for two days now and would really appreciate some help. I've imported data from a csv where one field contained html data encoded in base64.
The idea is to loop over every row and run FROM_BASE64 on it.
How do I structure a query that:
- Loops over all lines
- Calls FRON_BASE64 for each line
- Runs UPDATE (or similar functionality) on that same row and column
Context: I'm running MariaDB (MySQL equivalent).
Thanks for any help!
mysql sql mariadb
mysql sql mariadb
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
asked Nov 19 '18 at 18:11
MentorMentor
472213
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
asked Nov 19 '18 at 18:11
MentorMentor
472213
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
edited Nov 19 '18 at 18:24
Bill Karwin
377k63515670
377k63515670
asked Nov 19 '18 at 18:11
MentorMentor
472213
asked Nov 19 '18 at 18:11
MentorMentor
472213
asked Nov 19 '18 at 18:11
MentorMentor
472213
472213
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Typically base64 would be used for binary data. You probably shouldn't store the decoded data in the same column as the base64-coded string. If necessary you should ALTER TABLE to add a new column that is VARBINARY or BLOB type, to hold the binary data.
ALTER TABLE MyTable ADD COLUMN BinaryField BLOB;
You can then fill that column with an UPDATE statement:
UPDATE MyTable SET BinaryField = FROM_BASE64(EncodedField);
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
Thank you for that! The current data type of the column is string (I'm importing into a table I didn't make and shouldn't alter). Would I cause issues withUPDATE MyTable SET BinaryField = FROM_BASE64(Binary Field) ;. In case I was unclear, I don't need the original Base64 string.
– Mentor
Nov 19 '18 at 18:37
Figured it out.update thetable set thecolumn = FROM_BASE64(thecolumn)did the trick. This was simpler than I anticipated.
– Mentor
Nov 19 '18 at 18:47
You might want to learn about character sets.
– Bill Karwin
Nov 19 '18 at 19:25
The Base64 column should beTEXT CHARACTER SET ascii COLLATE ascii_bin. (There are other options, but this is the most logical.)
– Rick James
Nov 20 '18 at 3:44
@RickJames, like I mentioned in my comment, the data was being imported into a database belonging to an existing codebase. The only reason the string was base64 encoded was to make it easier to store html in a csv file without causing syntax issues.
– Mentor
Nov 21 '18 at 10:22
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380414%2fupdate-column-from-base64-to-string-natively-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Typically base64 would be used for binary data. You probably shouldn't store the decoded data in the same column as the base64-coded string. If necessary you should ALTER TABLE to add a new column that is VARBINARY or BLOB type, to hold the binary data.
ALTER TABLE MyTable ADD COLUMN BinaryField BLOB;
You can then fill that column with an UPDATE statement:
UPDATE MyTable SET BinaryField = FROM_BASE64(EncodedField);
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
Thank you for that! The current data type of the column is string (I'm importing into a table I didn't make and shouldn't alter). Would I cause issues withUPDATE MyTable SET BinaryField = FROM_BASE64(Binary Field) ;. In case I was unclear, I don't need the original Base64 string.
– Mentor
Nov 19 '18 at 18:37
Figured it out.update thetable set thecolumn = FROM_BASE64(thecolumn)did the trick. This was simpler than I anticipated.
– Mentor
Nov 19 '18 at 18:47
You might want to learn about character sets.
– Bill Karwin
Nov 19 '18 at 19:25
The Base64 column should beTEXT CHARACTER SET ascii COLLATE ascii_bin. (There are other options, but this is the most logical.)
– Rick James
Nov 20 '18 at 3:44
@RickJames, like I mentioned in my comment, the data was being imported into a database belonging to an existing codebase. The only reason the string was base64 encoded was to make it easier to store html in a csv file without causing syntax issues.
– Mentor
Nov 21 '18 at 10:22
add a comment |
Typically base64 would be used for binary data. You probably shouldn't store the decoded data in the same column as the base64-coded string. If necessary you should ALTER TABLE to add a new column that is VARBINARY or BLOB type, to hold the binary data.
ALTER TABLE MyTable ADD COLUMN BinaryField BLOB;
You can then fill that column with an UPDATE statement:
UPDATE MyTable SET BinaryField = FROM_BASE64(EncodedField);
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
Thank you for that! The current data type of the column is string (I'm importing into a table I didn't make and shouldn't alter). Would I cause issues withUPDATE MyTable SET BinaryField = FROM_BASE64(Binary Field) ;. In case I was unclear, I don't need the original Base64 string.
– Mentor
Nov 19 '18 at 18:37
Figured it out.update thetable set thecolumn = FROM_BASE64(thecolumn)did the trick. This was simpler than I anticipated.
– Mentor
Nov 19 '18 at 18:47
You might want to learn about character sets.
– Bill Karwin
Nov 19 '18 at 19:25
The Base64 column should beTEXT CHARACTER SET ascii COLLATE ascii_bin. (There are other options, but this is the most logical.)
– Rick James
Nov 20 '18 at 3:44
@RickJames, like I mentioned in my comment, the data was being imported into a database belonging to an existing codebase. The only reason the string was base64 encoded was to make it easier to store html in a csv file without causing syntax issues.
– Mentor
Nov 21 '18 at 10:22
add a comment |
Typically base64 would be used for binary data. You probably shouldn't store the decoded data in the same column as the base64-coded string. If necessary you should ALTER TABLE to add a new column that is VARBINARY or BLOB type, to hold the binary data.
ALTER TABLE MyTable ADD COLUMN BinaryField BLOB;
You can then fill that column with an UPDATE statement:
UPDATE MyTable SET BinaryField = FROM_BASE64(EncodedField);
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
Typically base64 would be used for binary data. You probably shouldn't store the decoded data in the same column as the base64-coded string. If necessary you should ALTER TABLE to add a new column that is VARBINARY or BLOB type, to hold the binary data.
ALTER TABLE MyTable ADD COLUMN BinaryField BLOB;
You can then fill that column with an UPDATE statement:
UPDATE MyTable SET BinaryField = FROM_BASE64(EncodedField);
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
answered Nov 19 '18 at 18:27
Bill KarwinBill Karwin
377k63515670
377k63515670
Thank you for that! The current data type of the column is string (I'm importing into a table I didn't make and shouldn't alter). Would I cause issues withUPDATE MyTable SET BinaryField = FROM_BASE64(Binary Field) ;. In case I was unclear, I don't need the original Base64 string.
– Mentor
Nov 19 '18 at 18:37
Figured it out.update thetable set thecolumn = FROM_BASE64(thecolumn)did the trick. This was simpler than I anticipated.
– Mentor
Nov 19 '18 at 18:47
You might want to learn about character sets.
– Bill Karwin
Nov 19 '18 at 19:25
The Base64 column should beTEXT CHARACTER SET ascii COLLATE ascii_bin. (There are other options, but this is the most logical.)
– Rick James
Nov 20 '18 at 3:44
@RickJames, like I mentioned in my comment, the data was being imported into a database belonging to an existing codebase. The only reason the string was base64 encoded was to make it easier to store html in a csv file without causing syntax issues.
– Mentor
Nov 21 '18 at 10:22
add a comment |
Thank you for that! The current data type of the column is string (I'm importing into a table I didn't make and shouldn't alter). Would I cause issues withUPDATE MyTable SET BinaryField = FROM_BASE64(Binary Field) ;. In case I was unclear, I don't need the original Base64 string.
– Mentor
Nov 19 '18 at 18:37
Figured it out.update thetable set thecolumn = FROM_BASE64(thecolumn)did the trick. This was simpler than I anticipated.
– Mentor
Nov 19 '18 at 18:47
You might want to learn about character sets.
– Bill Karwin
Nov 19 '18 at 19:25
The Base64 column should beTEXT CHARACTER SET ascii COLLATE ascii_bin. (There are other options, but this is the most logical.)
– Rick James
Nov 20 '18 at 3:44
@RickJames, like I mentioned in my comment, the data was being imported into a database belonging to an existing codebase. The only reason the string was base64 encoded was to make it easier to store html in a csv file without causing syntax issues.
– Mentor
Nov 21 '18 at 10:22
Thank you for that! The current data type of the column is string (I'm importing into a table I didn't make and shouldn't alter). Would I cause issues with
UPDATE MyTable SET BinaryField = FROM_BASE64(Binary Field) ;. In case I was unclear, I don't need the original Base64 string.– Mentor
Nov 19 '18 at 18:37
Thank you for that! The current data type of the column is string (I'm importing into a table I didn't make and shouldn't alter). Would I cause issues with
UPDATE MyTable SET BinaryField = FROM_BASE64(Binary Field) ;. In case I was unclear, I don't need the original Base64 string.– Mentor
Nov 19 '18 at 18:37
Figured it out.
update thetable set thecolumn = FROM_BASE64(thecolumn) did the trick. This was simpler than I anticipated.– Mentor
Nov 19 '18 at 18:47
Figured it out.
update thetable set thecolumn = FROM_BASE64(thecolumn) did the trick. This was simpler than I anticipated.– Mentor
Nov 19 '18 at 18:47
You might want to learn about character sets.
– Bill Karwin
Nov 19 '18 at 19:25
You might want to learn about character sets.
– Bill Karwin
Nov 19 '18 at 19:25
The Base64 column should be
TEXT CHARACTER SET ascii COLLATE ascii_bin. (There are other options, but this is the most logical.)– Rick James
Nov 20 '18 at 3:44
The Base64 column should be
TEXT CHARACTER SET ascii COLLATE ascii_bin. (There are other options, but this is the most logical.)– Rick James
Nov 20 '18 at 3:44
@RickJames, like I mentioned in my comment, the data was being imported into a database belonging to an existing codebase. The only reason the string was base64 encoded was to make it easier to store html in a csv file without causing syntax issues.
– Mentor
Nov 21 '18 at 10:22
@RickJames, like I mentioned in my comment, the data was being imported into a database belonging to an existing codebase. The only reason the string was base64 encoded was to make it easier to store html in a csv file without causing syntax issues.
– Mentor
Nov 21 '18 at 10:22
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53380414%2fupdate-column-from-base64-to-string-natively-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
