CODEWITHSUNDEEP
mysqlsqldatabaseconcatenation
Jay Zus
Jay Zus

Reputation: 573

Add a column in a table with the result of the concatenation of 2 fields of that row

I'm having a bit of trouble with something which I think must be pretty simple.

I have a table containing:

CREATE TABLE cities(  
 city_id INT PRIMARY KEY, 
 city_article TEXT,
 city_name  TEXT 
 );

So, the city_name is a simple field with for example name like 'PARIS' or 'NEW YORK'. city_article is mostly empty, except for some cities (like french cities) who need an article in front of them like 'LA ROCHELLE' which is set in the database as city_article = 'LA' city_name = 'ROCHELLE'

I'd like to create a new column containing the concatenation of city_article and city_name, separated with a '-' and replace every space with the same '-'.

So if we take something like 'THE NEW YORK' separated in city_article = 'THE' city_name = 'NEW YORK'

the third column would be city_concatenated = 'THE-NEW-YORK'

the trick is that since not all cities have an article, I don't want cities like 'PARIS' to become '-PARIS' after the operation.

Is there such a way to do that easily ?

TL;DR: how do I create a new column containing the result of 2 other columns whith one which can be NULL and the other one which must have every space replaced with '-' without putting a '-' where it's not needed due to the absence of the first field.

Upvotes: 0

Views: 757

Answers (2)

GarethD
GarethD

Reputation: 69759

I am not sure whether or not you could have an empty string in City_article, but I've assumed this could happen:

SELECT  CONCAT(CASE WHEN City_Article = '' OR City_Article IS NULL THEN '' ELSE CONCAT(City_Article, '-') END, REPLACE(city_name, ' ', '-'))
FROM    Cities;

In terms of adding this as a column, well I wouldn't. Since MySQL does not allow computed columns I'd recommend creating a view that stores the definition of this column. This ensures the full name is always up to date and an additional column doesn't require updating if the article or the city name are updated:

CREATE VIEW Cities_Ext 
AS
SELECT  City_ID,
        City_Article,
        City_Name,
        CONCAT(CASE WHEN City_Article = '' OR City_Article IS NULL THEN '' ELSE CONCAT(City_Article, '-') END,REPLACE(city_name, ' ', '-')) AS FullName
FROM    Cities;

Example on SQL Fiddle

Upvotes: 1

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

Put the first dash inside a COALESCE statement:

SELECT COALESCE(city_article + '-','') + REPLACE(city_name, ' ', '-')

Upvotes: 1

Related Questions