0

Given a result from a query like

table
-----
fieldSmarty,textBlbs
fieldExcel,textBlah

is there any way to extract them into two columns?

table                | field  | text 
-------------------------------------
fieldSmarty,textBlbs | Smarty | Blbs
fieldExcel,textBlah  | Excel  | Blah

If it makes it any easier, the strings are always going to start with field and text. The table header name is less relevant- splitting them would just be a great start.

Rio
  • 14,182
  • 21
  • 67
  • 107
  • 2
    The answer is here: http://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql – Paul Draper Oct 28 '13 at 00:07
  • Plz type your query so we can modify it accordingly – Airy Oct 28 '13 at 00:10
  • 1
    Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, and why they didn't work. – Kermit Oct 28 '13 at 00:11

1 Answers1

2

Edit: Adjusted to include Daniel P's adjustment

Is the comma always going to be there as well? You can do something like the following:

select foo, SUBSTR(foo, 6, INSTR(foo, ',')-6) AS field, 
       SUBSTR(foo, instr(foo, ',')+5) as text 
from table;

Syntax may be slightly off (don't have a mysql box to test with at the moment). the substring for the "field" column starts at 6 to remove the word "field". Instring gets the position of the beginning of the string (so, in the "smarty Blbs" example, it would return 12). So, if we want to omit the comma, we subtract one to stop at the 11th position. Then the substring of the "text" column starts after the comma and the word text and goes to the end of the string.

AgentBawls
  • 109
  • 12
  • `select foo, SUBSTR(foo, 6, INSTR(foo, ',')-6) AS field, SUBSTR(foo, instr(foo, ',')+5) as text from table1;` Tested and adjusted : http://sqlfiddle.com/#!2/f22a2/4/0 – Daniel P Oct 28 '13 at 00:30