Thursday, 8 August 2013

Update Column Value with Prefix and Sufix in MySql Database

Update particular column with specific prefix

update tablename set ColumnName= concat('prefix',ColumnName);

Update particular column with specific sufix

update tablename set ColumnName= concat(ColumnName,'sufix');

Remove prefix from any column in predefine char

UPDATE tablename  SET ColumnName = SUBSTRING(ColumnName,prefixLength) WHERE ColumnName RLIKE '^prefix';

Remove sufix from any column in predefine char

UPDATE tablename  SET ColumnName = SUBSTRING(ColumnName,sufixLength) WHERE ColumnName LLIKE '^sufix';

Example:
Below query update the vLongId field with "-" prefix;
update TableName set vLongId = concat('-',vLongId );

Below query update the vLongId field with "-" prefix;
UPDATE TableName SET vLongId = SUBSTRING(vLongId ,2) WHERE vLongId RLIKE '^-';

No comments:

Post a Comment

List OF BANK PAN Numbers

List OF BANK PAN Numbers Bank/Home Loan Providers PAN Number Allahabad Bank AACCA8464F Andhra Bank AABCA7375C Axis Bank...