CODEWITHSUNDEEP
mysqlsqlselectconcatenation
Michael Dinh
Michael Dinh

Reputation: 11

Spaces in Concat function in SQL

I'm attempting the following problem:

The capital of Mexico is Mexico City. Show all the countries where the capital has the country together with the word "City". Find the country where the capital is the country plus "City".

And I was wondering why this query is correct: 

SELECT name
FROM world
WHERE capital = CONCAT (name, ' city');

And yet this one isn't: 

SELECT name
FROM world
WHERE capital = CONCAT (name, 'city');

Why does this instance of the CONCAT function require a space before 'city'?

Upvotes: 1

Views: 9471

Answers (4)

Shushil Bohara
Shushil Bohara

Reputation: 5656

If you check properly there is space in the capital between name and city so the following query is not working 

SELECT name FROM world WHERE capital = concat(name, 'city');

So you should either remove space from capital using replace or add space in the name and city as below

SELECT name FROM world WHERE replace(capital, ' ', '') = concat(name, 'city'); --return value

Or

SELECT name FROM world WHERE capital = concat(name, ' city'); --return value

Or

SELECT name FROM world WHERE capital = concat(name, ' ', 'city'); --return value

Upvotes: 0

Brien Foss
Brien Foss

Reputation: 3367

Why does this instance of the concat function require a space before 'city'?

It's because there is literally a space between the capital name. You are concatenating a string with a ' ' + [city].

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated.

For instance, consider this test data (you can test it here)

;WITH world (country, capital) AS (
    SELECT 'Mexico','Mexico City' UNION ALL
    SELECT 'Guatemala','Guatemala City' UNION ALL
    SELECT 'Panama','Panama City' UNION ALL
    SELECT 'South Korea','Seoul' UNION ALL
    SELECT 'Vatican City','Vatican City'
    )

--verify  
--Mexico (Mexico City)
--Guatemala (Guatemala City)
--Panama (Panama City)
--NOT return Vatican City because 'Vatican City' <> 'Vatican City City'
SELECT * 
FROM world 
WHERE capital = CONCAT(country, ' City')

enter image description here

Upvotes: 0

SonniE
SonniE

Reputation: 131

The second entry would match only if the capital of the country was for example Mexicocity.

as it is concatenating the name with city like 'name' + 'city' = 'mexicocity' this won't match 'mexico city'

Upvotes: 1

crimson589
crimson589

Reputation: 1308

What is there to wonder about? spaces are part of a string, your string is "Mexico City", your name column is just "Mexico", if you concat "City" into it you get "MexicoCity" not "Mexico City".

Upvotes: 0

Related Questions