IRoveJS IRoveJS - 4 months ago 17
SQL Question

Extracting numerical values from mySQL column string value

I have a "person" column in a mySQL database that represents the age and weight of a person as a string separated by a comma.

Example:
"24,175"

I want to be able to separate and extract those values and cast them as numbers.

Example: turn "24,175" to

24 as age

175 as weight

So that I can write a query similar to the following

SELECT person
FROM TABLE
WHERE age>140 OR weight>1000


I want to be able to check for values that are not possible. i.e age>140 OR weight >1000.

I cannot modify the table/environment I'm working with

I only have access to queries.

I'm thinking about solving it this way

find the index where the comma exists. CHARINDEX(',',person)

Split the string into substrings using LEFT , RIGHT, CAST and CHARINDEX(',',person)

Cast age substring and weight substring to numbers using CAST(age AS INT) CAST(weight AS INT)

SELECT person
FROM TABLE
WHERE CAST(LEFT(person,CHARINDEX(',',person) AS INT)>150 OR CAST(RIGHT(person,CHARINDEX(',',person) AS INT) >1000


If I did anything wrong please correct me.
Are all the functions usable/supported by mySQL? (RIGHT, LEFT, CHARINDEX) Will this work?

Exception: Another value for this column could be "unknown". Will this cause errors if we're trying to check for the index of , if it doesn't exist in the string? Is there a way to include "unknown" cases in the result and have it output a message of "error, person not recognized"

Answer

you can also split is with SUBSTR_INDEX like this:

MariaDB [yourschema]> SELECT * FROM spliit;
+----+--------+
| id | d      |
+----+--------+
|  1 | 24,175 |
+----+--------+
1 row in set (0.03 sec)



MariaDB [yourschema]> SELECT
    ->     SUBSTRING_INDEX(d, ',', 1) AS age
    ->   , SUBSTRING_INDEX(d, ',', -1) AS weight
    ->
    -> FROM spliit;
+------+--------+
| age  | weight |
+------+--------+
| 24   | 175    |
+------+--------+
1 row in set (0.00 sec)

MariaDB [yourschema]>

sample

yes, you can direct calculate with it in MySQL

MariaDB [yourschema]> SELECT
    ->     SUBSTRING_INDEX(d, ',', 1)  + 2 AS age
    ->   , SUBSTRING_INDEX(d, ',', 1)  * 12 AS `month`
    ->   , SUBSTRING_INDEX(d, ',', -1) + 3 AS weight
    -> FROM spliit;

+------+-------+--------+
| age  | month | weight |
+------+-------+--------+
|   26 |   288 |    178 |
+------+-------+--------+
1 row in set, 1 warning (0.03 sec)

MariaDB [yourschema]>
Comments