CODEWITHSUNDEEP
postgresqlpostgresql-9.1
user3292755
user3292755

Reputation: 413

Replace String Contain multiple string in Postgresql

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

Upvotes: 0

Views: 157

Answers (1)

McNets
McNets

Reputation: 10807

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

Upvotes: 1

Related Questions