MySQL SELECT Conditional CONCAT

Question

I have a database which basically holds addresses

The table (tblAddress) looks like this...

housename    | housenumber | address1    | address2        | address3 | town      | postcode
Banana House |          29 | Acacia Road | Yellow Skin Way |          | Nuttytown | W1 1MP

When I search the database based on a postcode I want to be able to return and result like this...

Banana House,29 Acacia Road,Yellow Skin Way,Nuttytown,W1 1MP

So I need the housenumber concatenated with address1 IF address1 is populated. If not then concat with address2 or address3. Then the rest of the address to follow as per the example.

I tried using IF and CASE statements but can't seem to get anywhere near the output I'm after.

Hope that makes sense.

Amruth · Accepted Answer · 2017-10-05 13:00:29Z

2

You can do it by adding few concat operations.

Check below code it should work.

SELECT CONCAT(housename, CONCAT(" ",CONCAT(housenumber, CONCAT(" ",CONCAT_WS(' ,', 
                  NULLIF(address1, ''), 
                  NULLIF(address2, ''), 
                  NULLIF(address3, ''),
                  NULLIF(town, ''),
                  NULLIF(postcode, '')))))) AS concatedAddress FROM tblAddress;

edited Oct 5, 2017 at 13:00

answered Oct 5, 2017 at 12:24

Amruth

5,9122 gold badges30 silver badges41 bronze badges

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

2 Comments

user3411176 Over a year ago

I'm not waiting for the best answer, I'm testing as they come in, and they're coming in fast. I'll respond as soon as I've finished testing and can confirm the correct one.

user3411176 Over a year ago

I've now tested this and can confirm that it works. It just needs a slight modification to add a comma after the housename. Thank you for your time in resolving this for me. Very much appreciated.

MrCarrot · Accepted Answer · 2017-10-05 12:02:05Z

0

Use concat_ws() (concatenation with separator) along with nullif()

SELECT CONCAT_WS(',', NULLIF(housename, ''), 
                      NULLIF(housenumber, ''),  
                      NULLIF(address1, ''), 
                      NULLIF(address2, ''), 
                      NULLIF(address3, ''),
                      NULLIF(town, ''),
                      NULLIF(postcode, '')
       ) AS address FROM tblAddress

answered Oct 5, 2017 at 12:02

MrCarrot

2,8182 gold badges28 silver badges34 bronze badges

1 Comment

Raymond Nijland Over a year ago

That will result in Banana House,29,Acacia Road... topicstarter asked for Banana House,29 Acacia Road...

Darko Miletic · Accepted Answer · 2017-10-05 12:26:04Z

0

How about this:

SELECT 
       housename
      ,CONCAT( housenumber, CONCAT(' ', COALESCE(address1, '')), CONCAT(' ', COALESCE(address2, '')), CONCAT(' ', COALESCE(address3, '')) ) AS address
      ,town
      ,postcode 
FROM tblAddress;

answered Oct 5, 2017 at 12:26

Darko Miletic

1,4063 gold badges16 silver badges26 bronze badges

Comments

Elumalai Kaliyaperumal · Accepted Answer · 2017-10-05 12:51:56Z

0

Try like below,

 SELECT CONCAT_WS ( ", ",
    housename,
    CONCAT(housenumber, CONCAT_WS (", ", NULLIF(address1, ''), NULLIF(address2, ''), NULLIF(address3, ''))),
    town,
    postcode) AS address
FROM tblAddress

edited Oct 5, 2017 at 12:51

answered Oct 5, 2017 at 12:13

Elumalai Kaliyaperumal

1,5301 gold badge13 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT Conditional CONCAT

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related