CODEWITHSUNDEEP
phpmysqlsqlexplode
richelliot
richelliot

Reputation: 586

Select part of a field's value mysql

I have a database field which contains a value in a very specific format.

When I perform this following basic sql query:

SELECT post_content FROM wp_posts LIMIT 5

It returns the following resultset:

[download id="5219"]
[download id="953"]
[download id="958"]
[download id="3907"]
[download id="3909"]

I need to edit the query so that it only returns the numeric value of the string. So - instead of returning: [download id="5219"] , I want it to return 5219

Is this possible?

Thanks in advance :)

Upvotes: 1

Views: 897

Answers (2)

ekad
ekad

Reputation: 14614

Assuming the pattern is always [download id="x"] where you only want to take x, you can replace [download id=" and "] with an empty string using MySQL REPLACE function

SELECT REPLACE(REPLACE(post_content,'[download id="',''),'"]','') AS post_content 
FROM wp_posts LIMIT 5

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think the easiest way is to use substring_index(). The following will return the value as a number:

select substring_index(post_content, '"', 2) + 0

The following as a string:

select substring_index(substring_index(post_content, '"', 2), '"', -1)

Upvotes: 1

Related Questions