snipt

Ctrl+h for KB shortcuts

MySQL

Update portion of a string in MySQL

1
2
3
4
#Don't forget backup or make a copy before apply!
#http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string

update table set field = REPLACE(field, 'string', 'anothervalue') WHERE field LIKE '%string%';
https://snipt.net/embed/c92fe10d35e18b4c97149a8b2efc2c6d/
https://snipt.net/raw/c92fe10d35e18b4c97149a8b2efc2c6d/
c92fe10d35e18b4c97149a8b2efc2c6d
mysql
MySQL
4
2016-09-29T21:20:52
True
False
False
/api/public/snipt/102162/
update-portion-of-a-string-in-mysql
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="c1">#Don&#39;t forget backup or make a copy before apply!</span> </span><span id="L-2"><a name="L-2"></a><span class="c1">#http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string</span> </span><span id="L-3"><a name="L-3"></a> </span><span id="L-4"><a name="L-4"></a><span class="k">update</span> <span class="k">table</span> <span class="kt">set</span> <span class="n">field</span> <span class="o">=</span> <span class="k">REPLACE</span><span class="p">(</span><span class="n">field</span><span class="p">,</span> <span class="s1">&#39;string&#39;</span><span class="p">,</span> <span class="s1">&#39;anothervalue&#39;</span><span class="p">)</span> <span class="k">WHERE</span> <span class="n">field</span> <span class="k">LIKE</span> <span class="s1">&#39;%string%&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
%¨string%, MySQL, REPLACE, UDATE

MySQL

Find and Replace

1
2
3
-- Simple Find and replace. --
update [table_name]
set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
https://snipt.net/embed/cd35a4b5902c406014f7969b0ca5a8bc/
https://snipt.net/raw/cd35a4b5902c406014f7969b0ca5a8bc/
cd35a4b5902c406014f7969b0ca5a8bc
mysql
MySQL
3
2016-09-29T15:29:35
True
False
False
Apr 01, 2014 at 01:23 AM
/api/public/snipt/93761/
find-and-replace-4
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="c1">-- Simple Find and replace. --</span> </span><span id="L-2"><a name="L-2"></a><span class="k">update</span> <span class="p">[</span><span class="n">table_name</span><span class="p">]</span> </span><span id="L-3"><a name="L-3"></a><span class="kt">set</span> <span class="p">[</span><span class="n">field_name</span><span class="p">]</span> <span class="o">=</span> <span class="k">replace</span><span class="p">([</span><span class="n">field_name</span><span class="p">],</span><span class="s1">&#39;[string_to_find]&#39;</span><span class="p">,</span><span class="s1">&#39;[string_to_replace]&#39;</span><span class="p">);</span> </span></pre></div> </td></tr></table>
MySQL

SQL

Calculate age from birthday in MySQL

YEAR(NOW()) - YEAR(birthday) - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age
https://snipt.net/embed/b61d9cbe779f060ed3f64fcea2877340/
https://snipt.net/raw/b61d9cbe779f060ed3f64fcea2877340/
b61d9cbe779f060ed3f64fcea2877340
sql
SQL
1
2016-09-29T16:36:38
True
False
False
Jan 24, 2013 at 07:12 PM
/api/public/snipt/54435/
calculate-age-from-birthday-in-mysql
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="k">YEAR</span><span class="p">(</span><span class="n">NOW</span><span class="p">())</span> <span class="o">-</span> <span class="k">YEAR</span><span class="p">(</span><span class="n">birthday</span><span class="p">)</span> <span class="o">-</span> <span class="p">(</span><span class="n">DATE_FORMAT</span><span class="p">(</span><span class="n">NOW</span><span class="p">(),</span> <span class="s1">&#39;00-%m-%d&#39;</span><span class="p">)</span> <span class="o">&lt;</span> <span class="n">DATE_FORMAT</span><span class="p">(</span><span class="n">birthday</span><span class="p">,</span> <span class="s1">&#39;00-%m-%d&#39;</span><span class="p">))</span> <span class="k">AS</span> <span class="n">age</span> </span></pre></div> </td></tr></table>
MySQL, SQL

MySQL

MYSQL INSERT UPDATE

1
2
3
INSERT INTO table (column1, column2, column3, column4, column5)
VALUES (value1, value2, value3, value4, value5)
ON DUPLICATE KEY UPDATE column1=VALUES(column1), column3=VALUES(column3);
https://snipt.net/embed/7bdf47fd11584c8445bdc091a080e2f7/
https://snipt.net/raw/7bdf47fd11584c8445bdc091a080e2f7/
7bdf47fd11584c8445bdc091a080e2f7
mysql
MySQL
3
2016-09-29T16:12:06
True
False
False
Nov 21, 2012 at 01:38 AM
/api/public/snipt/50563/
mysql-insert-update
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="k">table</span> <span class="p">(</span><span class="n">column1</span><span class="p">,</span> <span class="n">column2</span><span class="p">,</span> <span class="n">column3</span><span class="p">,</span> <span class="n">column4</span><span class="p">,</span> <span class="n">column5</span><span class="p">)</span> </span><span id="L-2"><a name="L-2"></a><span class="k">VALUES</span> <span class="p">(</span><span class="n">value1</span><span class="p">,</span> <span class="n">value2</span><span class="p">,</span> <span class="n">value3</span><span class="p">,</span> <span class="n">value4</span><span class="p">,</span> <span class="n">value5</span><span class="p">)</span> </span><span id="L-3"><a name="L-3"></a><span class="k">ON</span> <span class="n">DUPLICATE</span> <span class="k">KEY</span> <span class="k">UPDATE</span> <span class="n">column1</span><span class="o">=</span><span class="k">VALUES</span><span class="p">(</span><span class="n">column1</span><span class="p">),</span> <span class="n">column3</span><span class="o">=</span><span class="k">VALUES</span><span class="p">(</span><span class="n">column3</span><span class="p">);</span> </span></pre></div> </td></tr></table>
MySQL
Copyrighted, illegal, or inappropriate content? Email support@snipt.net.