0

Is it possible to either combine or concatenate the following two statements?

RIGHT(wp_postmeta.meta_value, LENGTH(meta_value) -2)

LEFT(wp_postmeta.meta_value, LENGTH(meta_value) -2)

What I am ultimately trying to do is strip the first two and last two characters from a field (for display or output only...I am not trying to modify or replace anything in my database).

Below is my full query, which works great...except the output of wp_postmeta.meta_value is a URL encased with [" and "].

SELECT wp_download_log.ID, wp_download_log.download_date, wp_posts.post_title,

(SELECT group_concat(wp_terms.name separator ', ') FROM wp_terms 
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id 
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
WHERE taxonomy= 'dlm_download_category' and wp_posts.ID = wpr.object_id ) AS "Categories",

wp_postmeta.meta_value, wp_download_log.download_id, wp_download_log.version_id, wp_download_log.user_id, wp_download_log.user_ip, wp_download_log.user_agent
FROM wp_posts
LEFT JOIN wp_download_log ON wp_posts.ID = wp_download_log.download_id
LEFT JOIN wp_postmeta ON wp_download_log.version_id = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = '_files'

In there you will see that one column I am trying to output is wp_postmeta.meta_value where wp_postmeta.meta_key = '_files'.

Inserting one of my two statements I am trying to combine in place of wp_postmeta.meta_value works great. Inserting both works too...I just have two columns.

With my search for a unified solution coming up dry, I have been trying for the past hour or two to concatenate the two to get the contents of wp_postmeta.meta_value WHERE meta_key = '_files' into a single field, sans the first two and last two characters, but I have yet to figure out how.

Surely there is a SQL solution here without having to manually process the column in Excel with each export?

0

3 is the first position after the first 2 chars are removed. The length is reduced by 2 chars in the beginning + 2 characters at the end.

select MID(wp_postmeta.meta_value, 3, LENGTH(wp_postmeta.meta_value) -4 ) ...

simplied fiddle

3
  • Genius! This is exactly what I needed! There is no way I would have arrived at this myself. Thank you so much! – user2461674 Mar 5 '20 at 4:40
  • Looking through the available string functions is good for getting an idea of what is possible. Of course application code for handling this is a way to do this too ;-). Happy coding/SQLing and welcome to dba. – danblack Mar 5 '20 at 4:59
  • 1
    I'd prefer SUBSTRING( wp_postmeta.meta_value FROM 3 FOR LENGTH(wp_postmeta.meta_value) -4 ) - it seems to be more clear... – Akina Mar 5 '20 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.