concat: Adding string at the end of a field data

We can append a string or data to an existing data of a field by using concat function of MySQL. Here we are not updating or replacing an existing data with new one, we are just adding the string at the end of the field data.

For example we want to add site signature at the end of the comments posted by users. Here each user posts are different so we can't apply any update command so we will use concat to add the site signature to the each post kept inside a record field in our update command.

Here is the syntax

concat(field_name,"string to add")
Now let us see how it is used in a MySQL table query.

update photo_img set dtl=concat(dtl,'site_data to add') where gal_id='22'

The above command will add the string site_data to add at the end of each record dtl field for which gal_id is equal to 22

If we will not use Where condition in sql then all the records will be changed with concat query.

Adding / to dir name stored in MySql table

In our table we stored directory name as 'html_tutorial' for all the records. We want to update all the records by adding forward slash to the data inside dir name.
UPDATE  `table_name` SET dir=concat('/',dir)
This will add forward slash to the columns dir for all the records ( at left side ).

To add / at end of the data of all records we can use like this.
UPDATE  `table_name` SET dir=concat(dir,'/')
Try to change some part of the data by using replace command. Try to understand difference between replace and concat.

Combining two columns to display as one using select query

We can use concat function to join two columns and display them as single column along with other data. In our table we have first name and last name stored. Now using concat we will combine data of two columns and display them as only name.
SELECT concat(f_name,' ',l_name) as name,class  FROM `student_name`
This will return the following records.
nameclass
John DeoFour
NULLFour
NULLFive
Garry MillerFive
NULLFive
NULLNULL
Note that we have kept some null data in our records , in the above query if f_name or l_name is null then the total value after concat will be null. If we require that it should ignore the null data and display only the available data then we have to use COALESCE and display
SELECT concat(COALESCE(f_name,'-',' '),' ',COALESCE(l_name,'-')) as name,class  FROM `student_name`
With this we will get output like this.
nameclass
John Deo Four
Larry - Four
Ronald - Five
Garry Miller Five
- - Five
- Ruller NULL
Here is the sql dump of student_name table for your use.
CREATE TABLE IF NOT EXISTS `student_name` (
  `f_name` varchar(20) DEFAULT NULL,
  `l_name` varchar(20) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `student_name`
--

INSERT INTO `student_name` (`f_name`, `l_name`, `class`) VALUES
('John', 'Deo', 'Four'),
('Larry', NULL, 'Four'),
('Ronald', NULL, 'Five'),
('Garry', 'Miller', 'Five'),
(NULL, NULL, 'Five'),
(NULL, 'Ruller', NULL);

Number of User Comments : 5


Google+

mroz

06-02-2009

I tried but it didn't work. Which version of MySQL did you try? Mine is 4.2
smo

06-02-2009

What is the error message you are getting? Are you getting message saying function concat does not exit ?
Ramya

06-11-2009

it did work but not in the way i wanted.It was wacky it updated by id field.I mentioned the column name and string to append and but it updated by id field. i have no idea.
Jim

19-01-2010

This only works if the value of the field is not NULL.... FYI ;)
Neeraj

18-03-2010

CONCAT_WS() is the inbuilt function to come out of this issue. Example: update test_user set descrip = concat_ws(' ',descrip, 'SECOND') where Id=2

Post Comment This is for short comments only. Use the forum for more discussions.




Subscribe to our mailing list

* indicates required
Subscribe to plus2net




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2016 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer