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.
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
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
...
