home  |  class notes  |  homework  |  jsp help  |  mysql help

Some notes on CONCAT from Jordan Barrette

Your classmate Jordan Barrette was good enough to write up some notes on MySQL's CONCAT function, which is helpful for attractive displays of the output of your SQL queries.

Here's something that might come in handy, even though it's a little late to be able to use it in some final projects. It's called CONCAT, and it is a string function that can be used in MySQL. CONCAT stands for concatenate, which is exactly what it does. It's kind of similar to just placing "+" signs between Strings in Java, and since we've done so much of that, it didn't really surprise me that I wanted to use something similar when I returned a column for MySQL. But, in MySQL just putting "+"s between String will make MySQL think you want to perform addition of numbers, and so you'll get an error.

CONCAT accepts both strings and field names as input. Below is an example.

Say you have a table of names in a database. 

Table: Names
+----+--------------+-------------+
| ID |     Last     |    First    |
+====+==============+=============+
| 1  | Smith        |  John       |
+----+--------------+-------------+
| 2  | Shaw         |  Adam       |
+----+--------------+-------------+
| 3  | Harris       |  Ken        |
+----+--------------+-------------+

Querying the database with the following

SELECT CONCAT(Last,First) AS Name FROM Names;
would return this: 
+-------------+
|    Name     |
+=============+
|  SmithJohn  |
+-------------+
|  ShawAdam   |
+-------------+
|  HarrisKen  |
+-------------+

CONCAT also accepts string constants given that they are enclosed in quotes:

SELECT CONCAT(Last,',',First) AS Name FROM Names;
would return this: 
+--------------+
|    Name      |
+==============+
|  Smith,John  |
+--------------+
|  Shaw,Adam   |
+--------------+
|  Harris,Ken  |
+--------------+

This can come in handy for many obvious reasons explored during Java programming. The reason why I was looking for it was because in my final project I wanted to store IP addresses in 4 separate fields. An ip like 128.135.1.1 would be stored with 128,135,1,and 1 all in separate columns. So to display the IP in a friendly format on a web page, my JSP contains something like this:

SELECT CONCAT(IP1,'.',IP2,'.',IP3,'.',IP4) FROM Hardware WHERE ... ;

Editor's note: CONCAT_WS('.',IP1,IP2,IP3,IP4) does the same thing.

If this piques your interest, look at the documentation for both CONCAT and CONCAT_WS on the mySQL string functions page.

Scraper Source Code

The code from the scraper lecture in one big text file:

NBA-scraper.txt

A Database to Experiment With

http://polonium.cs.uchicago.edu:8180/adamshaw/webapps/DB/NBAQuery.jsp

mySQL JDBC Driver

To connect mySQL and Java/JSP, you will need the following file:

mysql-connector-java-3.0.6-stable-bin.jar

Handout

Getting Java, mySQL and Eclipse to work together.

mySQL Online Documentation

Some links of interest: