CODEWITHSUNDEEP
mysql
CruftyCraft
CruftyCraft

Reputation: 781

How do I get the type of a variable in MySQL?

I'm trying to change a table field that contains decimal numbers from varchar(255) to decimal(12,2). And before I do that, I'd like to find out if there is information that would get deleted in the process: are there any rows where this field contains something other than a decimal(12,2).

I'm stumped how to do this. Apparently there isn't a string function like is_numeric() in PHP. I already tried casting the field to decimal and then comparing it with the original string, but this returns TRUE even for obvious cases where it should not:

select ('abc' = convert('abc', decimal(12,2)));

returns 1

Any help? How do I find out if a string contains something other than a decimal in MySQL? Thanks.

Upvotes: 1

Views: 71

Answers (2)

JustDanyul
JustDanyul

Reputation: 14044

If you want to examine if the strings are actually floating points numbers, you could also use a regular expression. The following regex can help :)

SELECT '31.23' REGEXP '^[[:digit:]]+([.period.][[:digit:]]+)?$'; # returns 1
SELECT '31' REGEXP '^[[:digit:]]+([.period.][[:digit:]]+)?$'; # returns 1
SELECT 'hey' REGEXP '^[[:digit:]]+([.period.][[:digit:]]+)?$'; # returns 0

Upvotes: 0

CruftyCraft
CruftyCraft

Reputation: 781

Stupid me, I have to cast twice (to decimal and back to char), which makes it work: select ('abc' = convert(convert('abc', decimal(12,2)), char(255))); returns 0

Thanks.

Upvotes: 1

Related Questions