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')
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')
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.
CONCAT(f_name,' ',l_name) as name,class FROM `student_name`
This will return the following records.
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
CONCAT(COALESCE(f_name,'-',' '),' ',COALESCE(l_name,'-')) as name,
class FROM `student_name`
With this we will get output like this.
Joining three columns with separator
SELECT CONCAT ( f_name,'/',l_name,'/',class) as file_name
Here is the sql dump of student_name table for your use.
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.
My John Rob
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`