Extract a value from a string in a table?

Question

I am trying to extract a value from a string in a table like this below,

query table,

query_id    value
1           type={"page":"page"}&parent_id=10&image=on&content=on
2           type={"page":"page"}&parent_id=self
3           type={"category":"contact"}

as you can see the parent_id is in the query and sometimes is not.

I want to extract parent_id so I get this result,

query_id    page_id     value
1           10          type={"page":"page"}&parent_id=10&image=on&content=on
2           self        type={"page":"page"}&parent_id=self
3           null        type={"category":"contact"}

I try with this query,

SELECT 
    *,
    CAST(
        SUBSTRING(
            value,PATINDEX('%parent_id=%', value) + 8,(PATINDEX('%&%', substring(value,PATINDEX('%parent_id=%', value),50)) - 9)
            ) AS INT
        ) AS page_id
FROM query

however I get this error,

1064 - You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use
near 'INT ) AS page_id FROM query LIMIT 0, 30' at line 6

EDIT:

Maybe I should not be using PATINDEX as I tested it with the query below,

SELECT 
    *,
    SUBSTRING(
            value,PATINDEX('%parent_id=%', value) + 8,(PATINDEX('%&%', substring(value,PATINDEX('%parent_id=%', value),50)) - 9)
            ) AS test
FROM query

I get this error,

#1305 - FUNCTION mydb.PATINDEX does not exist

EDIT:

Got my answer,

SELECT 
    *,
    IF(LOCATE('parent_id=', value)>0,SUBSTRING_INDEX(SUBSTRING(value,LOCATE('parent_id=', value) + 10),'&',1),null)AS page_id
FROM query

And this is why people invented relation databases and normalisation. Normalise your database and the answer would have been `SELECT page_id` instead. — Berry Langerak, May 01 '12 at 13:10

score 3 · Answer 1 · answered May 01 '12 at 12:21

3

INT is already used in MYSQL. Use the right syntax

SELECT 
    *,
    CAST(
        SUBSTRING(
            `value`,PATINDEX('%parent_id=%', `value`) + 8,(PATINDEX('%&%', substring(`value`,PATINDEX('%parent_id=%', `value`),50)) - 9)
            ) AS `INT`
        ) AS `page_id`
FROM `query`

answered May 01 '12 at 12:21

Ron van der Heijden

14,803
7
58
82

1

Yep, always escape your fieldnames **and aliases** to avoid having MySQL mix it up with a reserved name (like INT). +1 – Oldskool May 01 '12 at 12:25
Are you using a single quote or a backtick (`)? It needs to be the backtick. – Buggabill May 01 '12 at 12:36

Extract a value from a string in a table?

1 Answers1