Compare string in database

Question

I have these entries in database.

enter image description here

Numbers at the end are versions, i.e 10.0, 9.1, 9.0. I need to compare the entries and I used a query like,

select * from Fault_Profile where PROFILE_NAME < 'DEFAULT_9.1';

But the entries are comapred like string, and returns values with 10.0 (I know it is obivous).

But is there any way using sql queries to extract number out of the string and compare it like numbers.

NOTE : This is old design in my project can't change schema can't change values.

pls use text than image so that we can copy paste the values! or give a sqfiddle link! — vhadalgi, May 29 '14 at 17:13
possible duplicate of [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) — Félix Adriyel Gagnon-Grenier, May 29 '14 at 17:13

score 0 · Answer 1 · answered May 29 '14 at 17:18

0

You can use SUBSTR() then retype to DOUBLE

SELECT *
FROM Fault_Profile
WHERE SUBSTRING(PROFILE_NAME, 9) < 9.1;

answered May 29 '14 at 17:18

MichalSv

567
5
10

score 0 · Answer 2 · answered May 29 '14 at 17:18

May not be an efficient one but you can try something like below, which will return only the part after _. i.e, 10.0, 9.1, 9.0

select 
substring('Default_9.0',
locate('_','Default_9.0')+1,
(length('Default_9.0') - locate('_','Default_9.0')))

So, per your query it should be

select * 
from Fault_Profile 
where 
substring(PROFILE_NAME,
locate('_',PROFILE_NAME)+1,
(length(PROFILE_NAME) - locate('_',PROFILE_NAME))) < 9.1

Linger · Answer 3 · 2014-05-29T17:45:41.767

SQL Fiddle:

SELECT * 
FROM Fault_Profile
WHERE CAST(RIGHT(PROFILE_NAME, CHARACTER_LENGTH(PROFILE_NAME) - INSTR(PROFILE_NAME, '_')) AS DECIMAL(3,1)) < 9.1
ORDER BY CAST(RIGHT(PROFILE_NAME, CHARACTER_LENGTH(PROFILE_NAME) - INSTR(PROFILE_NAME, '_')) AS DECIMAL(3,1));

Or if this looks better to you (SQL Fiddle):

SELECT m.PROFILE_NAME, m.ALARM_CLASS FROM 
(
  SELECT *, 
    CAST(RIGHT(PROFILE_NAME, CHARACTER_LENGTH(PROFILE_NAME) - INSTR(PROFILE_NAME, '_')) AS DECIMAL(3,1)) AS version
  FROM Fault_Profile f
) AS m
WHERE m.version < 9.1
ORDER BY m.version, m.ALARM_CLASS

The above query does assume that the characters after the _ will be the numeric version number. However, you can have as many characters as you need in front of the _. So for instance it will still work with; Beta_9.0 and Production_10.1.

score 0 · Answer 4 · answered May 29 '14 at 17:24

CREATE FUNCTION PROFILE_NAME_VERSION
(
    -- Add the parameters for the function here
    @PROFILE_NAME nvarchar(100)
)
RETURNS float
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar float
    SET @ResultVar=CAST(REPLACE(@PROFILE_NAME,'DEFAULT_','') as float)
    RETURN @ResultVar
END
GO

select * from Fault_Profile where dbo.PROFILE_NAME_VERSION(PROFILE_NAME) < 9.1

Replace the size of the function parameter for match your size

score 0 · Answer 5 · answered May 29 '14 at 17:41

0

you can use REPLACE too

SELECT  * 
FROM Fault_Profile
where REPLACE(PROFILE_NAME, 'Default_','')*1 < 9.1

answered May 29 '14 at 17:41

neiha

171
5

Compare string in database

5 Answers5