Stripping Data from Result Using Delimeter

Databases
1

I’m trying to query a distinct list of properties but many of these properties’ records often have the city and state appended to them.

Example:
Hilton Garden Inn Charlotte, NC
Embassy Suites - Los Angeles, CA

I’d like to drop everything after the “,” comma and would appreciate any suggestions.

My query works otherwise. Here’s the current SQL:

SELECT DISTINCT pm.meta_value
FROM hj_posts p
LEFT JOIN hj_postmeta pm ON p.id = pm.post_id
WHERE p.post_status = 'publish'
AND pm.meta_value != NULL
AND pm.meta_key = '_Company'
OR meta_key = '_property_name'
ORDER BY meta_value ASC
2 

select case when position(',' in meta_value ) > 1 then
            substring(meta_value from 1 for position(',' in meta_value ) - 1
       else meta_value end as meta_value
  from hj_postmeta pm
 where exists
     (select *
        from hj_posts p
       where p.id = pm.post_id
         and p.post_status = 'publish')
   and meta_key in ('_Company','_property_name')
 order 
    by meta_value
3 

select case when position(',' in meta_value) > 1 then
            substring(meta_value from 1 for position(',' in meta_value) - 1)
       else meta_value end as meta_value
  from hj_postmeta pm
 where exists
     (select *
        from hj_posts p
       where p.id = pm.post_id
         and p.post_status = 'publish')
   and meta_key in ('_Company','_property_name')
 order by meta_value

Added a missing ) for the substring function invocation.

4

in mysql, you can replace the CASE expressions with…

SELECT SUBSTRING_INDEX(meta_value,',',1) AS meta_value
  FROM ...
5

in mysql, you can replace the CASE expressions with…

SELECT SUBSTRING_INDEX(meta_value,',',1) AS meta_value
  FROM ...
6

Thank you. I’m looking into both of these suggestions.