0

I've spend the whole morning on this and I am absolutely stumped.

I have a mysql table, in which one of the columns are populated like this:

arrOpt['']={price:564.3,msrp:'779', d:'Width: 25"" |Height: 54"" |Weight: 33lbs',sku:6886939,ac:'260.00',ship:'221.95',cShip:'0.00',img:'503-13-35.jpg',imgT:'',oImg:'',shipW:'0',leadL:'7',leadU:'10',partNo:'503-13-35',invChk:'33rd',mfrStk:'',stkShip:'1',bo:'',desc:'',osum:'',upc:'702404269082',packQty:1,ship_via:'FreeTrk',freightUpgradeLvl1:'25.00',site:'home',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 15th'} "

there is also a possibility for it to be like this:

arrOpt['264596']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356623,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'3',leadL:'7',leadU:'14',partNo:'151 Blue',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Blue',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264583']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356624,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Celery',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Celery',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264590']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356625,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Chambray',invChk:'31st',mfrStk:'7',stkShip:'1',bo:'',desc:'',osum:'Chambray',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264597']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356626,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Ecru',invChk:'31st',mfrStk:'30',stkShip:'1',bo:'',desc:'',osum:'Ecru',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264601']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356627,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Lavender',invChk:'31st',mfrStk:'25',stkShip:'1',bo:'',desc:'',osum:'Lavender',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264603']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356628,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Maize',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Maize',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264608']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356629,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 Pink',invChk:'31st',mfrStk:'20',stkShip:'1',bo:'',desc:'',osum:'Pink',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'};arrOpt['264616']={price:30.25,msrp:'46.1519', d:'Length: 126"" |Height: 8.5""',sku:7356630,ac:'12.75',ship:'6.00',cShip:'0.00',img:'151_celery.jpg',imgT:'',oImg:'',shipW:'4',leadL:'7',leadU:'14',partNo:'151 White',invChk:'31st',mfrStk:'50',stkShip:'1',bo:'',desc:'',osum:'White',upc:'',packQty:1,ship_via:'Ground',freightUpgradeLvl1:'0.00',site:'baby',clearancePrice:'0.00',shipTimeCutOff:'', shipTime:'Mar 12th - Mar 19th'}

As you can see there are fields where arrOpt can be either blank, or containing multiple arrOpt with a number inside.

How would I structure the sql statement to grab the number inside the brackets? Like for example, I want to be able to grab "264596,264583,264590" etc from the second example.

Improve this question
4
  • So, the arrOpt column stores JSON(ish ?) data and you want to be able to retrieve that via string functions of mysql. Is that correct? Commented Apr 11, 2012 at 5:29
  • It's not really JSON, it's a javascript array. But it could range from either just 1 element to multiple elements. And yeah, if possible I want to use the string functions in mysql, because php is tooo slow. Commented Apr 11, 2012 at 5:45
  • Does this stackoverflow.com/questions/8334438/… help you? Commented Apr 11, 2012 at 5:55
  • nope, I can't use php to parse it, I have 2.5 million records to parse through, php would be too slow. I am however open to other languages that would suppose multithreading to accomplish this. Commented Apr 11, 2012 at 6:07

1 Answer 1

Reset to default
0

I don't have time to code this up, but you want to create a function using these links as a guide:

Pseudocode:

Pos = 0;
OutString = "";
Length = Len(InString);
WHILE LocationOf "arrOpt['" in right(InString,Length - Pos) > 0
    Start = Location + 8;
    End = Locationof "'" in right(InString,Length - Start + 1);
    OutString = concatenate(OutString,",",Mid(InString,Start,End);
    Pos = End + 1;
EndWhile
OutString = right(OutString,Len(OutString) - 1;
return OutString;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

looks like a step in the right direction, I'll try it out, thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.