CODEWITHSUNDEEP
mysqlsqlsubstring
Dudo1985
Dudo1985

Reputation: 289

Mysql select only numbers from substring result

I've this query

SELECT SUBSTRING(post_content,INSTR(post_content, '[table'),16) as substring,
ID FROM `wp276_posts`
WHERE `post_content` LIKE '%[table%' 
AND `post_status` = 'publish' 
ORDER BY `ID` ASC

that returns this results:

enter image description here

How can I select only the the number part of the substring?

e.g. of the needed result:

+-----------+------+
| substring | ID   |
+-----------+------+
| 22        | 5072 |
+-----------+------+
| 67        | 5757 |
+-----------+------+
| 54        | 8550 |
+-----------+------+

Upvotes: 1

Views: 410

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

Yes, you can use SUBSTRING() and INSTR() functions combination :

SELECT SUBSTRING(post_content,INSTR(post_content, 'id=')+3,
                 INSTR(post_content, ' /') - INSTR(post_content, 'id=') - 3
                 ) as substring, ID
  FROM t
 WHERE .....

Demo

Upvotes: 0

nbk
nbk

Reputation: 49375

With ficc´xed text you can use REPLACE

SELECT 
    REPLACE(REPLACE(post_content," /]",""),"[tabelid= ","") as substring
    , ID 
FROM 
    `wp276_posts` 
WHERE 
    `post_content` LIKE '%[table%' 
    AND `post_status` LIKE 'publish' 
ORDER BY `ID` ASC

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

SUBSTRING_INDEX might work here:

SELECT
    post_content,
    SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, '[table id=', -1), ' ', 1) AS id
FROM wp276_posts
WHERE
    post_content LIKE '%[table%' AND
    post_status = 'publish'   -- you seem to want equivalence here
ORDER BY
    ID;

The inner call to SUBSTRING_INDEX above returns everything to the right of [table id=. The outer call then takes what is to the left of the space which is presumed to follow the id number.

Upvotes: 1

Related Questions