import list_price -thousands separator

[dakruhm]

How should I handle the LIST_PRICE field in MYSQL?

The outside data comes through with commas. For example: 599,000
If I make the data type INT, the prices are truncated at the comma: 599
If I make the data type VARCHAR, the prices show correctly however it kills the whole searching feature since you can't < or > a VARCHAR.

Thanks in advance.

[mkrotchie]

You should probably store it as an INT and use parsing to strip out the comma before you insert it into the DB, and add it in after as you pull it out to display it.

[dakruhm]

wow, that was fast. Thanks Arizona...

I was hoping there was some kind of mysql featue I didn't know about.

I guess I'm off to brush up on my SED'ing...

[mkrotchie]

If you're using PHP using the explode function is probably the easiest.. unless you are curious about regex's.

// PHP Example
$pizza = "piece1 piece2 piece3 piece4 piece5 piece6";
$pieces = explode(" ", $pizza);
echo $pieces[0]; // piece1
echo $pieces[1]; // piece2

[dakruhm]

I think REGEX is the way to go in this case.

Exploding >60,000 listings and >100 fields would probably work but would take a long time compared to LOAD DATA LOCAL INFILE.

It's a ZIPPED txt file delimited by a pipe, like so: ||||||. I just have to fool around with REGEX to SED the comma in the price but not in the other fields.

People have to run into this all the time. You'd figure MLS's and state associations would recognize they need to change this from their end and offer plain numbers. After all, price is kinda important. Better yet, offer a read only XML that only authorized brokers and vendors could tap into.

[dakruhm]

Just for completeness...

[DMike]

That depends from MLS to MLS. Probabily the MLS you have the feed from keeps the info how the agents enter it