[RESOLVED] CAST AS...

perpetualshaun

I'm trying to pull some results from MySQL, and I'm hoping there is a way to do this...

I think I can use one of two of the cast functions, but not sure how to and/or in which order. Let's say that I have a list or listing results like this:

listing_building_size
27,000 rsf
9,360 sf
10,000 rsf
49,776 sf
218,232 rsf

Can I use a couple CAST functions to strip out the commas AND the rsf and sf at the end of each value? I'd like to get a result set like this:

listing_building_size
27000
9360
10000
49776
218232

Is this possible? We are using MySQL - 4.1.20 on this server.

Thanks,
Shaun

NogDog

Untested (and unsure 😉 ):

$sql = "SELECT IF(CAST(col_name AS INT) > 0, REPLACE(col_name, REGEX '[^0-9]', ''), col_name) AS alias_name . . .";

perpetualshaun

I think I'm getting closer... here's the query I just tried and the error that I received from MySQL:

SQL query: Documentation

SELECT DISTINCT IF( CAST( listing_building_size AS UNSIGNED INTEGER ) >0, REPLACE( listing_building_size, REGEX "[^0-9]", "" ) , listing_building_size ) AS building_size

MySQL said: Documentation
#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 '"[^0-9]", ""), listing_building_size) AS building_size' at line 1

Lars_Berg

select trim(trailing ' ' from replace(substring(listing_building_size from 1 
   for position(' ' in listing_building_size)),',',''))
   from t

You can't use regular expressions in replace.

liquorvicar

Or change your DB structure so that numbers are stored as numbers in the DB.