CODEWITHSUNDEEP
phpmysqlregex
user1535312
user1535312

Reputation: 3

how do i search between complicated numbers on mysql query?

I have this number 4.40-4.85X3.60 in a column called MM.

i want to search for numbers that are 3 points bigger or lower from each number through mysql for exampel:

[4.37-4.43]-[4.82-4.88]X[0-9] , the number after X can be any number.

I tried using regular expressions but i didn't come with something that really work, please advise.

Upvotes: 0

Views: 88

Answers (2)

spencer7593
spencer7593

Reputation: 108370

One approach is to "split" that string up to get the separate components you want from it. Assuming that string always uses the '-' and 'X' to separate the three components, the expressions in this query will "split" it up for you: 

SELECT SUBSTRING_INDEX(@n,'-',1) AS s1
     , SUBSTRING_INDEX(SUBSTRING_INDEX(@n,'-',-1),'X',1) AS s2 
     , SUBSTRING_INDEX(@n,'X',-1) AS s3 
  FROM (SELECT @n := '4.40-4.85X3.6') n

Then you could convert each of those expressions to numeric.

Note that MySQL may (silently) lose digits of precision, may return "0" for strings that can't be converted to numeric, or MySQL may throw an exception or warnings.

So, take care with selecting the DECIMAL(m,n) scale and precision, to avoid overflows and to properly handle rounding/truncation. All of the data shown in your example fits a DECIMAL(3,2), but that is just an example, not a guarantee for all data.

I'd do the conversion of the "search argument" string in an inline view, and join that to the table to be searched.

Something like this:

SELECT t.MM
  FROM mytable t
  JOIN (SELECT CONVERT(SUBSTRING_INDEX(@n,'-',1)                        ,DECIMAL(6,2)) AS s1
             , CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(@n,'-',-1),'X',1),DECIMAL(6,2)) AS s2 
             , CONVERT(SUBSTRING_INDEX(@n,'X',-1)                       ,DECIMAL(6,2)) AS s3 
          FROM (SELECT @n := '4.40-4.85X3.6') n
        ) p
     ON ABS(p.s1 - CONVERT(SUBSTRING_INDEX(t.MM,'-',1),DECIMAL(6,2))) 
        <= .03
    AND ABS(p.s2 - CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(t,MM,'-',-1),'X',1),DECIMAL(6,2))
        <= .03

The JOIN predicates are just a matter of subtracting the (derived) numeric value from the table from the (derived) numeric value from the search parameter, and checking the difference.

Again, as I noted before, with the convert to DECIMAL operation, be aware of issues with strings not in the expected format, values that are too large to fit the scale (overflows), and rounding/truncation. You may get unexpected results (MySQL may lose precision and scale silently, or may throw exceptions and/or warnings. (The behavior is dependent on the sql_mode setting for the session.)

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324620

Use separate columns for the three numbers, and that would solve all your problems.

Otherwise, use something like:

WHERE
    CAST(SUBSTR(MM FROM 1 FOR (@tmp1:=POSITION("-" IN MM))) AS DECIMAL(3,2))
        BETWEEN 4.37 AND 4.43
    AND
    CAST(SUBSTR(MM FROM @tmp1 FOR POSITION("X" IN MM)-@tmp1) AS DECIMAL(3,2))
        BETWEEN 4.82 AND 4.88

but bear in mind that this completely screws over any index you might have on your table.

Upvotes: 2

Related Questions