{ Berbagi, Menulis, Dan Mengajar } Ilmu…

DELIMITER $$ USE `coba`$$ DROP TRIGGER `TriggerCopy`$$ CREATE /*[DEFINER = { user | CURRENT_USER }]*/ TRIGGER `TriggerCopy` AFTER INSERT ON `tabel1` FOR EACH ROW BEGIN /*UPDATE tabel2 SET nama = REPLACE(New.Nama,’081′,’+6281′);*/ INSERT INTO coba.tabel2 (id,Nama,Alamat) VALUE ( NEW.Id,NEW.Nama,NEW.Alamat); UPDATE tabel2 SET nama = REPLACE(NEW.Nama,’081′,’+6281′); END$$ DELIMITER ; … Read entire article »

DECLARE btstelepon1 INT; SELECT LOCATE(‘+’,new.SenderNumber) INTO btstelepon1; IF btstelepon1 > 0 THEN SELECT REPLACE(NEW.SenderNumber,’+62′,’0′) INTO notelp; SELECT COUNT(*) INTO jumlah FROM list_user where no_telepon = notelp; ELSE SELECT NEW.SenderNumber INTO notelp; END IF; … Read entire article »

… Read entire article »

Sometime you might facing problem to replace the string or data in mysql and you have many row inside your table.  Here is the solution you can put in to SQL command line in phpmyadmin, this solution is work for mysql too.  Before begin please back all your sql 1st, in case you make any mistake. Open phpmyadmin -> click on database -> click on table name -> click on SQL tab on top then enter the command below: UPDATE tablename SET field = replace(field, "SearchString", "ReplaceString"); please change: tablename field SearchString ReplaceString according your table name and field. you can also add where Clause ( add condition for this sql statemen ) UPDATE tablename SET field = replace(field, "SearchString", "ReplaceString") WHERE field2='SomeValue'; … Read entire article »

MySQL provides wide selection of functions which may be called during a SQL statement to make changes to text values. The following table lists the most frequently used functions in this category. Some example of how to use these functions are included after the table. For extensive details on how to use each of these functions refer to the MySQL Reference Guide: <SCRIPT LANGUAGE=’JavaScript’ TYPE=’text/javascript’ > document.write(‘<a href=”http://a.tribalfusion.com/h.click/apmRoPoTjIXF7gUtrUm6fJnsfopWMH5Erf3WIr3AnZapbUZc0GvPXsr40cBxpTfR5bJPVFnGVAjWQErQSsrpQt7s1d7xT6jy4sMUXFBDU6im5mQ8R6fH3tnsXW3AmtAN4PYP3sngTsUaWGjlR6QyUHJPWoTCr9FZdSCuHN9aUMSvkxRiLs63UVnyZdmSAn0mFrWo7QuTeIZdEuidj/http://clk.atdmt.com/INM/go/347254610/direct/01/955368118/” target=”_blank”><img src=”http://view.atdmt.com/INM/view/347254610/direct/01/955368118/” /></a>’);</SCRIPT> <NOSCRIPT> <A HREF=’http://a.tribalfusion.com/h.click/apmRoPoTjIXF7gUtrUm6fJnsfopWMH5Erf3WIr3AnZapbUZc0GvPXsr40cBxpTfR5bJPVFnGVAjWQErQSsrpQt7s1d7xT6jy4sMUXFBDU6im5mQ8R6fH3tnsXW3AmtAN4PYP3sngTsUaWGjlR6QyUHJPWoTCr9FZdSCuHN9aUMSvkxRiLs63UVnyZdmSAn0mFrWo7QuTeIZdEuidj/http://clk.atdmt.com/INM/go/347254610/direct/01/955368118/’ TARGET=’_blank’ > <IMG SRC=’http://view.atdmt.com/INM/view/347254610/direct/01/955368118/’ BORDER=’0′ > </A> </NOSCRIPT> Name Description ASCII() Returns numeric value of left-most character BIN() Returns a string representation of the argument BIT_LENGTH() Returns length of argument in bits CHAR_LENGTH() Returns number of characters in argument CHAR() Returns the character for each integer passed CHARACTER_LENGTH() The same as CHAR_LENGTH() CONCAT_WS() Returns concatenate with separator CONCAT() Returns concatenated string CONV() Converts numbers between … Read entire article »

Workshop Requirements You should have completed Parts One, Two, Three, Four and Five of this series. You should also have access to the MySQL command line client software. You should also have full permissions on a database. Introduction Thus far when retrieving stored data we have simply displayed the results of any query. MySQL can do more that this and has many built in functions that can transform data to meet our requirements. These include: Date Functions - used to manipulate the display format of a date as well as calculate time. String Functions – can manipulate a text string Numeric Functions - can manipulate figures Summarising Functions - output meta results from a query There are also Control Functions that can be used to give conditionality to queries. Date Functions Before looking at … Read entire article »

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake. The syntax of REPLACE is REPLACE(text_string, from_string, to_string) MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement. For example: update … Read entire article »

Basic usage The REPLACE() function takes three parameters: the string or column name to do the replacement on what to look for and what to replace it with The following example replaces the ‘aaa’ part of ‘aaa bbb ccc’ with ‘xyz’ and the column returned from the SQL query will contain ‘xyz bbb ccc': SELECT REPLACE('aaa bbb ccc', 'aaa', 'xyz'); If you were doing this against the column “foo” you would do this instead: SELECT REPLACE(foo, 'aaa', 'xyz'); My example In my case I had a column called ‘image_small’ and ‘image_large’ with example data like so: +------------+--------------+-------------+ | content_id | image_small | image_large | +------------+--------------+-------------+ | 1 | small1.jpg | big1.jpg | | 26 | small26.jpg | big26.jpg … Read entire article »

Setting constraints and rules in the database is better than writing special code to handle the same task since it will prevent another developer from writing a different query that bypasses all of the special code and could leave your database with poor data integrity.   For a long time I was copying info to another table using a script since MySQL didn’t support triggers at the time. I have now found this trigger to be more effective at keeping track of everything. This trigger will copy an old value to a history table if it is changed when someone edits a row. Editor ID and last mod are stored in the original table every time someone edits that row; the time corresponds to when it was … Read entire article »

mysql> mysql> mysql> CREATE TABLE Employee( ->     id            int, ->     first_name    VARCHAR(15), ->     last_name     VARCHAR(15), ->     start_date    DATE, ->     end_date      DATE, ->     salary        FLOAT(8,2), ->     city          VARCHAR(10), ->     description   VARCHAR(15) -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> delimiter $$ mysql> mysql> CREATE TRIGGER myTrigger ->   BEFORE INSERT ON employee ->   FOR EACH ROW -> BEGIN ->   DECLARE row_count INTEGER; -> -> SELECT COUNT(*) ->     INTO row_count -> FROM employee -> WHERE id=NEW.id; -> ->   IF row_count > 0 THEN -> UPDATE employee ->        SET salary = salary+NEW.salary -> WHERE id=NEW.id; ->   ELSE -> INSERT INTO employee (id,salary) VALUES(NEW.id,NEW.salary); -> END IF; -> -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description) ->              values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer'); ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description) ->               values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'Vancouver','Tester'); ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description) ->               values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester'); ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description) ->               values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager'); ERROR 1442 (HY000): Can't update table 'employee' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description) ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester'); ERROR 1442 (HY000): Can't update table 'employee' in stored … Read entire article »