Get embedded number from varchar field?

[cmayo]

Hi all,

Google hasn't offered much hope so I thought I'd ask the guys who know everything...

Anyone know of a MySQL string/regex function that'd select the "85" from a varchar containing something like "Nationals - $85?

 

[towerbase]

I think you need to be more specific about what you want.

For example, Do you simply want the last two characters of the varchar?

Or anything to the right of '$' ?

Or anything to the right of '- $' ?

The possibilities are almost endless.

There are quite a few MySQL functions for VARCHARs and CHARs.

Check out the MySQL website for functions such as SUBSTRING, SUBSTRING_INDEX, LOCATE, REVERSE, LEFT, RIGHT.



Andrew
Hampshire, UK

 

[cmayo]

Good point. I'm looking to select the first series of consecutive digits embedded in a string whose format varies widely, i.e.

Nationals $85
55 / hr
62.50 (in this case I'd need the whole float value)
cabling 75/phone 95

 

[darrellblackhawk]

I don't believe you can do this with MySQL. MySQL doesn't have a 'matches' collection like other RegEx implementations.

I believe you'll have to resort to doing this in the language you're using to talk to MySQL.

You can determine if a prospective value is in a column, but that's about it.
i.e.

[tt]
SELECT 'David spent $45.60 for the dinner' as testfield, 'David spent $45.60 for the dinner' REGEXP '[0-9.]+' as has_a_match
testfield                  has_a_match
================================================
David spent $45.60 for...  1

SELECT 'David spent nothing for the dinner' as testfield, 'David spent nothing for the dinner' REGEXP '[0-9.]+' as has_a_match
testfield                   has_a_match
================================================
David spent nothing for...  0
[/tt]

 

[cmayo]

Hi Darrell,

Yeah, that's pretty much what Google had turned up. Bummer, but if the database had been designed right in the first place, I wouldn't have to resort to an awkward kludge to get what I'm after. Oh well, maybe I can sell the guy a few more hours to fix his app.

Thanks,
Chuck

 

[hvass]

You really need something like regexp_like not available in regular MySQL but various flavours available as udf's try

http://udf-regexp.php-baustelle.de/trac/

 

[cmayo]

Wow, very cool, hvass. I'll have to see if I can get the UDFs to compile on my ISP's host (shell, but not root access) and my client's host (no shell). If so, those regex functions look like exactly what I'm after.

I'm also wondering now if the MDB2 abstraction layer used by the app offers any better support for regexes... back to Google!

Thanks again!