Replace String Contain multiple string in Postgresql

Question

Shipping address to persons contain multiple text inside it , for example in this 4 Rows of consumer address.

     |         Address Line                 |
     |--------------------------------------|
     |APARTMENT 5800 springfield GARDENS CIR|
     |--------------------------------------|
     |APT 5800 springfield GARDENS CIR      |
     |--------------------------------------|
     |2567 south pay street                 |
     |--------------------------------------|
     |Office 222 Market , VA 048946         |

How can i add another column that contain certain character such as Address Line contain "Apartment" or "APT" will become "Apartment" and Address Line contain "Street" will become "Household" , Address Line contain "Office" will become "Office building" and "Unknown" for Address Line not included.

This is the desired table after the query included with PostgreSql

         |         Address Line                 |      Location      |
         |--------------------------------------|--------------------|
         |APARTMENT 5800 springfield GARDENS CIR|   Apartement       |
         |--------------------------------------|--------------------|
         |APT 5800 springfield GARDENS CIR      |   Apartement       |
         |--------------------------------------|--------------------|
         |2567 south pay street                 |   Household        |
         |--------------------------------------|--------------------|
         |Office 222 Market , VA 048946         |   Office building  |
         |--------------------------------------|--------------------|

This is my query :

 SELECT ORDER_TABLE.guest_email,
        CASE WHEN ORDER_SHIPPINGADDRESS.LINE1 IN ('Apartement', 'apt') Then 'Apartement'
        WHEN ('street') then 'household'
        WHEN ('Office') then 'Office Building'
        ELSE 'Location Unknown' END as Location

My trial so far by using Case When however it ended error like this

ERROR: argument of CASE/WHEN must be type boolean, not type record

Are there any functions that can do the desired table above? thanks

McNets · Accepted Answer · 2017-02-09 07:48:29Z

1

You should repeat ORDER_SHIPPINGADDRESS.LINE1 INin each WHEN

SELECT ORDER_TABLE.guest_email,
    CASE WHEN ORDER_SHIPPINGADDRESS.LINE1 IN ('Apartement', 'apt') Then 'Apartement'
         WHEN ORDER_SHIPPINGADDRESS.LINE1 IN ('street') then 'household'
         WHEN ORDER_SHIPPINGADDRESS.LINE1 IN ('Office') then 'Office Building'
    ELSE 'Location Unknown' END as Location

answered Feb 9, 2017 at 7:48

McNets

10.9k3 gold badges39 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

Replace String Contain multiple string in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related