CODEWITHSUNDEEP
sqloracle-databaseselect
Insomnia
Insomnia

Reputation: 101

Oracle string compare in query

I have two string and want to know, if in alphabetically sorted list the string2 is before string1, is the same, or comes after string1.

Something like STRCMP (string1, string2) for MySql.

Is there some kind of built in function, that isn't that popular, or should I use a non-function approach manually comparing each char (if that is even considerable)?

Thanks in advance.

Upvotes: 3

Views: 23866

Answers (2)

Dave CK
Dave CK

Reputation: 263

You'll need to consider nulls and case.

In PL/SQL you could do something like this...

declare 

  BEFORE CONSTANT PLS_INTEGER := -1;
  AFTER CONSTANT PLS_INTEGER := 1;
  SAME CONSTANT PLS_INTEGER := 0;

  NULLS_FIRST CONSTANT VARCHAR2(1) := CHR(9);
  NULLS_LAST CONSTANT VARCHAR2(1) := CHR(127);

  string1 varchar2(100);
  string2 varchar2(100);

  result pls_integer;

  --If this was a function then these would be your input parameters
  inputString1 varchar2(100) := 'A'; --try null here
  inputString2 varchar2(100) := 'b';
  --swap this over if you want nulls first
  nullPos VARCHAR2(1) := NULLS_LAST; --try swapping to NULLS_FIRST

begin

  string1 := upper(nvl(inputString1,nullPos));
  string2 := upper(nvl(inputString2,nullPos));

  result := case when string1 < string2 then BEFORE 
             when string1 = string2 then SAME
             else AFTER end;

  dbms_output.put_line(result);           

end;

The values chosen for NULLS_FIRST and NULLS_LAST are just arbitrary but outside of the standard a-z A-Z so that a null value will get placed either before or after a non null one, and so two null values will return 0 (same).

If you aren't bothered about case i.e A comes after b then you can ignore the upper function code.

You could put the plsql code (or similar) in a function and call it from your SQL or you could do 

SELECT case when upper(nvl(string1,CHR(127))) < upper(nvl(string2,CHR(127))) then -1
            when upper(nvl(string1,CHR(127))) = upper(nvl(string2,CHR(127))) then 0
            else 1 
            end
...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Yes, it is <. You can use boolean comparators to get "alphabetic" ordering. I put that in quotes because it depends on the collation of the strings, but it is usually reasonable.

In a where clause:

where string1 < string2

If you want a value, then this works in both Oracle and MySQL:

select (case when string1 < string2 then 1 else 0 end) as string_compare_flag

Upvotes: 5

Related Questions