<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body dir="auto">
Thanks so much! What a simple fix. Darn fancy quotes indeed.
<div><br>
</div>
<div>Appreciate your help. <br>
<br>
<div id="AppleMailSignature" dir="ltr">Danielle Butler
<div><br>
<div><br>
</div>
</div>
</div>
<div dir="ltr"><br>
On Dec 20, 2018, at 8:56 PM, Blake Carver <<a href="mailto:blake.carver@lyrasis.org">blake.carver@lyrasis.org</a>> wrote:<br>
<br>
</div>
<blockquote type="cite">
<div dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Helvetica,sans-serif;" dir="ltr">
<p style="margin-top:0;margin-bottom:0">Maybe it's just the copy and paste has left you with those darn "fancy quotes", I brought up MySQL 8 here and these both worked, but after I made sure to put the regular ' in there because when I copied and pasted your
message it had the curly things and both commands barfed with:</p>
<p style="margin-top:0;margin-bottom:0">"ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',’ FROM title)' at line 2"</p>
<div><br>
</div>
<div>It's not super obvious in there, but the single quotes ' are curved, and not a plain '</div>
<div><br>
</div>
<p></p>
<p style="margin-top:0;margin-bottom:0">Here's what worked:</p>
<p style="margin-top:0;margin-bottom:0"><br>
</p>
<p style="margin-top:0;margin-bottom:0"></p>
<div>MySQL [archivesspace]> UPDATE archival_object SET display_string = replace(display_string, ',,', ',');</div>
<div>Query OK, 1 row affected (0.10 sec)</div>
<div>Rows matched: 18501 Changed: 1 Warnings: 0</div>
<div><br>
</div>
<div><span style="font-size: 12pt;">MySQL [archivesspace]> UPDATE archival_object SET title = TRIM(TRAILING ',' FROM title);</span><br>
</div>
<div></div>
<div>Query OK, 5 rows affected (0.12 sec)</div>
<div>Rows matched: 18501 Changed: 5 Warnings: 0</div>
<div><br>
</div>
Not sure how this will get formatted or reformatted from the mail programs, so you may need to do it again.
<p></p>
</div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b>
<a href="mailto:archivesspace_users_group-bounces@lyralists.lyrasis.org">archivesspace_users_group-bounces@lyralists.lyrasis.org</a> <<a href="mailto:archivesspace_users_group-bounces@lyralists.lyrasis.org">archivesspace_users_group-bounces@lyralists.lyrasis.org</a>>
on behalf of Danielle Butler <<a href="mailto:dbutler@cals.org">dbutler@cals.org</a>><br>
<b>Sent:</b> Thursday, December 20, 2018 6:26:47 PM<br>
<b>To:</b> Archivesspace Users Group<br>
<b>Subject:</b> [Archivesspace_Users_Group] Deleting duplicate commas - MySQL woes</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div id="x_divtagdefaultwrapper" dir="ltr" style="font-size:12pt; color:rgb(0,0,0); font-family:"Calibri Light","Helvetica Light",sans-serif,EmojiFont,"Apple Color Emoji","Segoe UI Emoji",NotoColorEmoji,"Segoe UI Symbol","Android Emoji",EmojiSymbols">
<div style="">
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">We’re trying to get rid of duplicate commas in our data in component descriptions. We manually included commas when we were using Archivists' Toolkit, and we migrated all of that data. Now
we'd like to clean it up so that every description doesn't have two commas if we were to ever more to the PUI. </span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif"><br>
</span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">I was given a command for an older MySQL server version, and modified it using the MySQL Server v. 8 manual.</span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif"></span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">I believe this is the appropriate way to format the command, but I am getting syntax errors on the first command. I've scoured the manual and can't sort out what is causing the error. </span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif"><br>
</span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">Does anyone see an apparent issue with my syntax that I'm missing? Again, these commands are for v. 8. </span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif"><br>
</span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif"></span></p>
<p style="color:rgb(33,33,33); font-size:11pt; font-family:Calibri,sans-serif,serif,EmojiFont; margin-right:0px; margin-left:0px">
<span style="color:rgb(0,119,170); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">REPLACE</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(0,119,170); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">INTO</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> archival_object </span><span style="color:rgb(0,119,170); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">VALUES</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(153,153,153); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">(</span><code style="font-family:"Courier New""><span style="color:rgb(47,57,65); font-size:9pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,249,249); padding:0px; border:1pt solid rgb(233,235,237)">display_string</span></code><span style="color:rgb(47,57,65); font-size:10.5pt; font-family:"Segoe UI",sans-serif,serif,EmojiFont"> </span><span style="color:rgb(153,153,153); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">,</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(102,153,0); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">',,'</span><span style="color:rgb(153,153,153); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">,</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(102,153,0); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">','</span><span style="color:rgb(153,153,153); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">);</span><span style="color:black; font-size:12pt; font-family:"Calibri Light",sans-serif,serif,EmojiFont"></span></p>
<p style="color:rgb(33,33,33); font-size:11pt; font-family:Calibri,sans-serif,serif,EmojiFont; margin-right:0px; margin-left:0px">
<span style="color:rgb(0,119,170); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">SELECT</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(221,74,104); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">TRIM</span><span style="color:rgb(153,153,153); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">(</span><span style="color:rgb(0,119,170); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">TRAILING</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(102,153,0); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">','</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(0,119,170); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">FROM</span><span style="color:black; font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248)"> </span><span style="color:rgb(102,153,0); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">'title'</span><span style="color:rgb(153,153,153); font-size:9.5pt; font-family:Consolas,serif,EmojiFont; background-color:rgb(248,248,248); padding:0px; border:1pt none windowtext">);</span></p>
<p></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif"><br>
</span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">Thanks for any advice you can give, </span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif"><br>
</span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<a name="x__MailAutoSig" id="LPlnk575980" class="x_OWAAutoLink"><span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif; color:#1F4E79">Danielle Butler, CA | Archivist</span></a></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style=""><span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">Butler Center for Arkansas Studies | Central Arkansas Library System
</span></span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style=""></span><a href="http://www.butlercenter.org/" id="LPlnk320163" class="x_OWAAutoLink" style="color:rgb(5,99,193); text-decoration:underline"><span style=""><span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">www.butlercenter.org</span></span><span style=""></span></a><span style=""><span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">
</span></span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style=""><span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">100 Rock Street</span></span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style=""><span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">Little Rock, AR 72201</span></span></p>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
<span style=""><span style="font-size:12.0pt; font-family:"Calibri Light",sans-serif">501-320-5724</span></span></p>
<span style=""></span>
<p style="margin:0in 0in 0.0001pt; font-size:11pt; font-family:Calibri,sans-serif">
</p>
</div>
</div>
</div>
</div>
</blockquote>
<blockquote type="cite">
<div dir="ltr"><span>_______________________________________________</span><br>
<span>Archivesspace_Users_Group mailing list</span><br>
<span><a href="mailto:Archivesspace_Users_Group@lyralists.lyrasis.org">Archivesspace_Users_Group@lyralists.lyrasis.org</a></span><br>
<span><a href="http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group">http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group</a></span><br>
</div>
</blockquote>
</div>
</body>
</html>