CODEWITHSUNDEEP
sql
Eslam Hamdy
Eslam Hamdy

Reputation: 7386

How to extract numbers within a specified range from a column?

I have a column in my schema which contains values as follows:

male, Brown, Blue, Small body build, 1.63

male, Brown, Blue, Small body build, 1.85

male, Brown, Blue, Small body build, 1.75

i want to make a query that extracts all the objects which has numbers lies between 1.63 and 1.75, Note that the numbers specified above are embedded in a string as you see the 3 examples above, and note also that these whole string is stored in one column, any idea on how to do that?

Upvotes: 0

Views: 516

Answers (3)

armitage
armitage

Reputation: 192

While the solutions provide work with the small sample set provided they may not work with all possible data in the column. If for instances you have data such as the data below

male, Brown, Blue, Small body build, 1.63
male, Brown, Blue, Small body build, 1.85
male, Brown, Blue, Small body build, 1.75
male, Brown, Blue, Small body build, 11.63
male, Brown, Blue, Small body build, 11.85
male, Brown, Blue, Small body build, 11.75

then extracting data using the right(col,4) solution would also return those rows with a value of 11.64 and 11.75 as the right(col,4) wile ignore the 1 representing the 10's.

The right(col,4) solution also ignores the overhead of possible string to int conversion and also assumes that right(col,4) is always an int.

A better solution would be to find the last ',' in the column and then take the substring of that and compare to required range.

An even better solution and one that would be much more efficient would be to break the data into separate columns such as Sex, HairColour, EyeColour, Build, Height etc. This would allow far better database performance, allow for better data extraction/querying etc

Upvotes: 0

Ron.B.I
Ron.B.I

Reputation: 2766

select * from tableName 
where convert(int, substr(columnName,LENGTH(columnName)-4,4)) BETWEEN 1.75 AND 1.63

This should work without limiting the Integer part to some syntax ((d.dd) for example) but requires it to be of length 4.

Edit

Between has more elegant usage.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can do this in many SQL dialects as:

select *
from t
where right(col, 4) between '1.63' and '1.75'

The string comparison works because the numbers have the decimal place in the same position.

Upvotes: 1

Related Questions