CODEWITHSUNDEEP
sqlsql-server
Real Gem
Real Gem

Reputation: 49

How to apply filter for percentage when it contains strings along with it in SQL Server

I wrote this query to filter out the results below 38 percentage. But when I execute I get the random result with no errors and not the expected results.

select Name, Percentage
from datamining.dbo.Register
where percentage <= '30%-40%'
   or percentage <= 'total 40% obtained'

The actual table:

Name      Percentage
----      ----------
Andrew    30%-40%
Angel     35%
Bob       total 60%
Clare     total 38% obtained

The expected result:

Name      Percentage
----      ----------
Andrew    30%-40%
Angel     35%
Clare     total 38% obtained

There are strings present along with percentage and the query which I wrote doesn't give me the expected results.

Assist me on how to recreate the condition which I have passed so I can achieve the expected result.

Upvotes: 0

Views: 611

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You could split the string and make the comparison:

select r.*
from datamining.dbo.Register r
where exists (select 1
              from string_split(r.percentage, ' ') s
              where try_cast(replace(s.value, '%', '') as int) < 38
             );

However, I would say that you have a fundamental problem with your data model. It sounds like you are trying to store a range of values. You should probably use percentage_low and percentage_high or something like that. A string is the wrong way to store these values -- unless, of course this is coming from some third party and you are stuck with this format.

Upvotes: 0

Sarath KGW
Sarath KGW

Reputation: 21

The table should in correct format, here percentage field is in string.

you can compare strings with the filtering conditions, but cannot be compared with '' Mathematical comparisons

Upvotes: 0

Madhukar
Madhukar

Reputation: 1242

Assuming the percentage is less than 100, below code computes the numeric value within the string column and gives you the list of percentages which are less than or equal to 38. This will not work in all cases as the percentage column is not normalized.

SELECT Name, Percentage
FROM datamining.dbo.Register
WHERE SUBSTRING(Percentage, PATINDEX('%[0-9]%', Percentage),2) <= 38

Upvotes: 1

Related Questions