concat to append string to the existing data of MySQL table

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

SELECT CONCAT('string1' , '-','string2')

Output

string1-string2

Joining strings by using CONCAT and handling NULL value.

For example, we want to add a 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 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'

Adding / to dir name stored in MySql table

In our table, we stored the directory name as 'html_tutorial' for all the records. We want to update all the records by adding forward slash to the data inside the dir name.

UPDATE  `table_name` SET dir=concat('/',dir)

This will add forward slash to the columns dir for all the records (on the 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.

Concat with null data

If null data is present in our record, then we can convert it to empty string and they use concat to add strings.
To find out null data we will use ifnull()

UPDATE  `table_name` SET dir=concat(ifnull(dir,''),'/')

Combining two columns to display as one using select query

We can use the Concat function to join two columns and display them as a single column along with other data. In our table we have a first name and last name stored. Now using concat we will combine data of two columns and display them as the only name.

SELECT f_name,l_name,
CONCAT(f_name,' ',l_name) as name,class  FROM `student_name`

This will return the following records.

f_name	l_name	name	class
John	Deo	John Deo	Four
Larry			Four
Ronald			Five
Garry	Miller	Garry Miller	Five
			Five
	Ruller

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.

SELECT CONCAT(Null,'-','String2')

Output

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 f_name,l_name,
CONCAT(COALESCE(f_name,'-',' '),' ',COALESCE(l_name,'-')) as name,
class  FROM `student_name`

With this we will get output like this.

f_name	l_name	name	class
John	Deo	John Deo	Four
Larry		Larry -	Four
Ronald		Ronald -	Five
Garry	Miller	Garry Miller	Five
		- -	Five
	Ruller	- Ruller

Joining three columns with separator

SELECT CONCAT ( f_name,'/',l_name,'/',class) as file_name 
FROM `student_name`

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);

Finding non integer values in an Integer field using concat

We have stored mark in the student table. But there are some string data is stored in the mark column and mark column is set as Varchar field. How to select these non-numeric values?

SELECT name, mark,(mark*1) AS  mark2 FROM student

For all numeric values in the above query, we will get both columns the same value. When a string is multiplied by 1 we get the output as zero. Now we will use this concept to list out which are not matching.

SELECT name, mark FROM student   WHERE CONCAT('',(mark*1)) !=mark

This will give us non-numeric value in mark field.

id	name	class	mark	sex
4	Krish Star	Four	wrong data	female
7	My John Rob	Fifth	string2	male

To test this code, change the mark column to Varchar and add some string data to mark field.

Add Bold html tag to data

SELECT concat('<b>',concat_ws(f_name,' ',l_name,'</b>') ) as name,
class  FROM `student_name`

CONCAT_WS : concatenate with separator
SQL String References substring_index to get part of string using delimiter
CONCAT is used to create Date sub groups in Date column
SQL file of CONCAT queries

Subscribe to our YouTube Channel here

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

concat: Adding string to a field data

Adding / to dir name stored in MySql table

Concat with null data

Combining two columns to display as one using select query

Joining three columns with separator

Finding non integer values in an Integer field using concat

Subscribe