CODEWITHSUNDEEP
mysql
André Teixeira
André Teixeira

Reputation: 2562

Set MySQL field according to substring comparison

I`m working on a data extraction from MySQL server and in several situations I'm using onquery substitutions. In example if I have a null field, it will assign an empty string for some fields, like:

ifnull(`negotiation_type`, "") AS negotiation_type

Now my need is to make similar test, but using substrings. I have a url field, and based on it`s value I want to set another field value, called property_type.

All the URLs will have the substring house or apartment, like:

http://www.example.com/?pics=true/tree-house-over-the-tree
http://www.example.com/?pics=true/blue-apartment-under-the-tere

And I`m wondering if composing the LOCATE function with any other mySQL function I will be able to make the property_type value assignment on SELECT.

Anyone have an idea?

Thanks

Upvotes: 0

Views: 90

Answers (1)

Gaëtan
Gaëtan

Reputation: 66

This one would do it I presume:

SELECT IF(LOCATE('HOUSE', `url`) > 0, 'HOUSE',IF( 
LOCATE('APPARTEMENT', `url`) > 0, 'APPARTEMENT', 'OTHER')) AS property_type

Upvotes: 1

Related Questions