Spaces in Concat function in SQL

Question

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'?

@MichealDinh Please take a moment and view the edits I've made to your question, try to use a similar approach to future questions :) — Brien Foss
– Brien Foss, Commented Jan 28, 2018 at 5:07

SonniE · Accepted Answer · 2018-01-28 02:13:46Z

1

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'

answered Jan 28, 2018 at 2:13

SonniE

1314 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

crimson589 · Accepted Answer · 2018-01-28 02:13:00Z

0

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".

answered Jan 28, 2018 at 2:13

crimson589

1,4202 gold badges23 silver badges44 bronze badges

Comments

Brien Foss · Accepted Answer · 2018-01-28 02:40:07Z

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')

Shushil Bohara · Accepted Answer · 2018-01-28 02:41:17Z

0

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

answered Jan 28, 2018 at 2:41

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

Spaces in Concat function in SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related