SQL Tips - String Concatenation
Robert Froehling – robert.froehling@gmail.com
Concatenation Methods
MSSQL
	string1 + string2

MySQL
	CONCAT(string1,string2,...)?
	CONCAT_WS(‘ ‘,string1,string2,...)?

Oracle
	CONCAT(string1,string2)?
	string1 || string2
What Do We Have?
EMPID
FNAME
MNAME
LNAME
1
John
Andrew
Doe
2
Jim
Bob
Brown
3
Bill
NULL
Smith
What Do We Want?
FirstName LastName
FirstName MiddleName LastName
FirstName MiddleInitial LastName
FirstInitial LastName
LastName, FirstName
FirstName LastName
MSSQL
SELECT fname + ‘ ‘ + lname AS ‘fullname’
FROM employee

MySQL
SELECT CONCAT_WS(‘ ‘,fname,lname) AS ‘fullname’
FROM employee

Oracle
SELECT fname || ‘ ‘ || lname AS ‘fullname’
FROM employee
FirstName MiddleName LastName
MSSQL
SELECT
	fname + ‘ ‘ + mname + ‘ ‘ + lname AS ‘fullname’

MySQL
SELECT
	CONCAT_WS(‘ ‘,fname,mname,lname) AS ‘fullname’

Oracle
SELECT
	fname || ‘ ‘ || mname || ‘ ‘ || lname AS ‘fullname’
FirstName MiddleName LastName
MSSQL – NULL Result
SELECT
	fname + ‘ ‘ + mname + ‘ ‘ + lname AS ‘fullname’

MSSQL – Proper Result
SELECT
	fname + ISNULL((‘ ‘ + mname + ‘ ‘),’ ’) + lname AS ‘fullname’
FirstName MiddleName LastName
Oracle – Extra Space
SELECT
	fname || ‘ ‘ || mname || ‘ ‘ || lname AS ‘fullname’

Oracle – Proper Result
SELECT
	fname || NVL2(mname,(‘ ‘ || mname || ‘ ‘),’ ‘) || lname AS ‘fullname’
FirstName MiddleInitial LastName
MSSQL
SELECT
	fname + ISNULL((‘ ‘ + LEFT(mname,1) + ‘ ‘),’’) + lname AS ‘fullname’

MySQL
SELECT
	CONCAT_WS(‘ ‘,fname,SUBSTRING(mname FROM 1 FOR 1),lname) AS ‘fullname’

Oracle
SELECT
	 fname || NVL2(mname,(‘ ‘ || SUBSTR(mname,1,1) ||
	 ‘ ‘),’ ‘) || lname AS ‘fullname’
FirstInitial LastName
MSSQL
SELECT
	LEFT(fname,1) + ‘ ‘ + lname AS ‘fullname’

MySQL
SELECT
	CONCAT_WS(‘ ‘,SUBSTRING(fname FROM 1 FOR 1),lname) AS ‘fullname’

Oracle
SELECT
	SUBSTR(fname,1,1) || ‘ ‘ || lname AS ‘fullname’
LastName, FirstName
MSSQL
SELECT lname + ‘, ‘ + fname AS ‘fullname’

MySQL
SELECT CONCAT_WS(‘, ‘,lname,fname) AS ‘fullname’

Oracle
SELECT lname || ‘, ‘ || fname AS ‘fullname’
UDF
SELECT UDF_FULLNAME(fname,mname,lname) AS ‘fullname’
FROM employee
UDF - MSSQL
CREATE FUNCTION UDF_FULLNAME (
	@nfname VARCHAR(25),
	@nmname VARCHAR(25),
	@nlname VARCHAR(25)?
)?
RETURNS VARCHAR(100)?
BEGIN
	DECLARE @result VARCHAR(100)?
	SET @result = @nfname + ISNULL((‘ ‘ + LEFT(@nmname,1) + ‘ ‘),’’) + @nlname
	RETURN @result
END
UDF - MySQL
CREATE FUNCTION UDF_FULLNAME (
	nfname VARCHAR(25),
	nmname VARCHAR(25),
	nlname VARCHAR(25)?
)?
RETURNS VARCHAR2(100)?
BEGIN
	DECLARE result VARCHAR(100) DEFAULT NULL;
	SET result = CONCAT_WS(‘ ‘,nfname,SUBSTRING(nmname FROM 1 FOR 1),nlname);
	RETURN result;
END UDF_FULLNAME;
UDF - Oracle
CREATE FUNCTION UDF_FULLNAME (
	nfname IN VARCHAR2(25),
	nmname IN VARCHAR2(25),
	nlname IN VARCHAR2(25)?
)?
RETURNS VARCHAR2(100)?
IS
	result VARCHAR2(100) DEFAULT NULL;
BEGIN
	result := nfname || NVL2(nmname,(‘ ‘ || SUBSTR(nmname,1,1) || ‘ ‘),’ ‘) || nlname;
	RETURN result;
END UDF_FULLNAME;