String into a number 1

[FB1]

Mysql 5.2

Good Afternoon all

have a field which is a string that I need to convert to a number.
in crystal I would convert the field as such val({doses}).

I can use the VAL() statement to strip out the non numberic characters.
can this be done in mysql

the characters I want to strip out are at the end of the number

"mc"
" mc"
"u"
" iu"
"mcg"

The number can be from 1 to 99999

typical data looks like 20mcg or 20 mcg or 20u or 20 u or 20iu or 20 iu.

Why the field is not a number thats another story.

Would most appreciate your help with this

 

[feherke]

Hi

In other SQL dialects [tt]trim()[/tt] has a second parameter containing the characters to remove or have a [tt]translate()[/tt] function to remove characters by transliteration or have a [tt]regexp_replace()[/tt] function to allow complex transformations.

In MySQL, depending on the frequency of this task, I would either use more [tt]replace()[/tt] calls or write a user-defined function.

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[r937]

you don't actually have to do anything special yourself, like trimming or replacing, just assign the string to the numeric column

mysql silently makes the conversion from left to right until it hits the first non-numeric character

but you do have to override the data truncation errors by using IGNORE

test it for yourself --

CREATE TABLE test_numeric
( foo VARCHAR(9)
, bar INTEGER
);
INSERT INTO test_numeric ( foo ) VALUES
 ( '10mc' )
,( '20 mc' )
,( '5u' )
,( '15 iu' )
,( '937 mcg' )
;
UPDATE IGNORE test_numeric SET bar = foo 
;
SELECT * FROM test_numeric
;

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[feherke]

Hi

Great trick, Rudy.

Feherke.
[link feherke.github.com/]

http://feherke.github.com/

[/url]

 

[FB1]

Thank you Rudy and Feherke

manage to work out a way to do this with the following query

0+replace(replace(replace(replace(replace(pre.prunit, 'm',''), 'c',''), 'g',''), 'u',''), 'i','') as units,

But thank you both for all your time

 

[r937]

fb1, when your conversion fails after a new unit is discovered, give my method a try

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon