Member Avatar for trektrak

I would love to know, if it's possible to get a specific data from a specific mysql table..

Let say on the table itself has a Varchar ... mixed of words and floats..

and then ... I want to pull only the float from the table using php...

can it be done ??

  • 4 Contributors
  • 8 Replies
  • 189 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by trektrak

Dani AI

Generated

A VARCHAR column that mixes free text with numbers is fine for quick storage, but it makes querying and math error-prone. Long-term: add a dedicated numeric column (DECIMAL for exact values, FLOAT for imprecise measurements), populate it once from the existing text, validate results, then switch code/queries to the numeric column. That gives correct sorting, indexing and avoids repeated parsing work. As and noted, pattern matching is a common extraction method; the following offers practical alternatives and deployment notes.

A simple, regex-free PHP trick that pulls the first floating value from a mixed string (useful for a quick one-off script) uses sscanf to skip leading text and read a float:

$txt = "Profile: Joe; height=5.5ft; notes=active";
if (sscanf($txt, "%*[^0123456789-]%f", $num) === 1) {
    // $num now holds 5.5 (float)
    printf("%f\n", $num);
}

Notes: this returns the first numeric token; it will fail on numbers that use comma decimals (replace ',' with '.' first) or strings with thousands separators (strip them). If the number is identified by a nearby label (e.g., "height"), locate that word first (strpos/locate) and parse the substring that follows to avoid grabbing the wrong value.

For in-database extraction (MySQL 8+), REGEXP_SUBSTR can pull a numeric substring and CAST it into DECIMAL:

SELECT CAST(REGEXP_SUBSTR(col, '[-+]?[0-9]*\\.?[0-9]+') AS DECIMAL(7,3)) AS extracted
FROM mytable
WHERE col REGEXP '[0-9]';

For older MySQL versions, run the application-side extraction and UPDATE the new numeric column. Always back up data, sample-check many rows, and pick DECIMAL precision to suit stored values.

Recommended Answers

All 8 Replies

Member Avatar for pritaeas

Can you give an example of your table and the data that is in it, and an example of the output you require ?

Member Avatar for kevindougans

if you are looking to pull a float out of a var char field that has for example text wrapped around it (i.e. "Temp: 26.2 Degrees") then I would suggest using preg_match or something alike to do a regular expression to extract it. I'm sure there is other ways too.

Member Avatar for trektrak

yeah ... something like that ... it's a long var char...

something like... "hello, my name is jack.. my height is 5.5 feet"

let say .. I just want to pull only "5.5"

Member Avatar for kevindougans 
$fullstring = "hello, my name is jack.. my height is 5.5 feet";
preg_match_all('/[0-9].[0-9]/', $fullstring, $matches);
$count = count($matches[0]);
# this will tell you how many times "x.x" was found in string
echo $count; 

# this will print out each "x.x" it found
for ($i = 0 ; $i < $count ; $i++ ) 
{
echo $matches[0][$i];
echo '<br/>';
}
Member Avatar for IIM

Use pattern matching for it....Use can see how to do this from the examples in this link

<?php
preg_match('/^\d+$/', $price[1]);
?>
Edited by IIM because: n/a
Member Avatar for kevindougans

You can also try

$var = '122.34343The';
$float_value_of_var = floatval($var);
echo $float_value_of_var; // 122.34343
Member Avatar for pritaeas

The following regex would also match 45.67 and -45

/-?[0-9]+(\.[0-9]+)?/
Member Avatar for trektrak

tq guys... I'll give a try later..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.