Extract category_id number from string

Question

I have string like this:

product_id=
category_id=340
flypage=
page=
page_title=
show_page_title=1
pageclass_sfx=
menu_image=-1
secure=0

How can I extract the category_id? I've tried to get numeric value from the field, but it I get 340110 instead of 340

show us how you got 340110 and we will suggest modifications — Codeek, Dec 12 '14 at 14:26
Not enough information here. Is this entire string stored in a column of a MySQL database table? What did you use to attempt to get the numeric value? Is there application code involved here in a language like PHP? Please provide more information about your platform, storage, and code. — Michael Berkowski, Dec 12 '14 at 14:26
Yes, the entire string is stored in MySQL column. I'm using the following solution to strip the non-numeric chars from string: http://stackoverflow.com/a/12057337/1281067 I'd like to avoid any application code in this case... — George Sharvadze, Dec 12 '14 at 14:32

score 1 · Answer 1 · answered Dec 12 '14 at 14:32

If you are looking to extract the value from the above pattern and looks like they are separated with \n you can get the value as

mysql> select substring_index(substring_index('product_id=
category_id=340
flypage=
page=
page_title=
show_page_title=1
pageclass_sfx=
menu_image=-1
secure=0','category_id=','-1'),'\n',1) as val;
+-----+
| val |
+-----+
| 340 |
+-----+
1 row in set (0.00 sec)

You may use in your query as

select substring_index(substring_index(col_name,'category_id=','-1'),'\n',1) as val;

Extract category_id number from string

1 Answers1