Fubs

Fubs are “frequently used bits” of code that I keep forgetting, so I post them here to keep track of them.

MySQL

• replacing strings [source]
  update table_name set field = replace(field, 'foo', 'bar') where instr(field, 'foo') > 0;
• cutting off the final period (for example) in a field [source]
  update table_name set field = substring(field, 1, char_length(field) - 1) where right(field,1) = '.'
• making a new user account and granting privileges on a database
  CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON mydb.* TO 'myuser'@'localhost';
• the JOIN post [source]
  All of the 9 (or is it 12?) different ways to join two tables.
• how to number rows in mysql [source]
  SELECT id, col1,col2, @rownum := @rownum + 1 AS rank
FROM table1, (SELECT @rownum := 0) as r
where condition1 = 1 ORDER BY col2 ASC
• search for contents of field a contained in field b
  SELECT myid,mytext FROM mytable WHERE mytext LIKE concat('%',myid,'%')
  If one just says:
  SELECT myid,mytext FROM mytable WHERE mytext LIKE '%myid%',
  it searches mytext for the string ‘myid’, not the value of the field myid

regex

• getting something in the middle of two other things
  /a(.*)[ab]/
  nb: using “|” is a frequent mistake:
  /a(.*)a|b/
  this will get a(.*)a or b!
• capitalizing part of a string
  Use \u
  $ename =~s/^Ou Yang(.)/Ouyang \u$1/ if $bname =~ /^欧阳/;

Perl

• how to increment a var in a substitution [source]
  s/xxx/${\(++$cnt)}/g
• load an anonymous array onto a named array
  push(@array,[$a, $b, $c]);
• put results of a table query using dbi directly into an array
  one column query
  my @data = @{$dbh->selectcol_arrayref(qq{select col from table where col > ”})};

  multi column query
  my @data = @{$dbh->selectall_arrayref(qq{select cola,colb,colc from table where cola != colb})};

Command line (windows)

• Find all the unanswered emails in the primary mailbox, but not in the social or promotions boxes [source]
  category:primary is:unread