SQL Order by List of Strings

SQL Order By list of strings?

Try using this:

select * from table 
order by FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot')

Sort an array of strings in SQL

The source table has a column with a JSON array.

That's why it is a perfect case to handle it via SQL Server JSON API.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID int IDENTITY PRIMARY KEY, jArray NVARCHAR(100));
INSERT @tbl (jArray) VALUES
('[7235, 6784]'),
('[3235, 2334]'), 
('[9245, 2784]'),
('[6235, 1284]');
-- DDL and sample data population, end

SELECT t.*
    , Result = QUOTENAME(STRING_AGG(j.value, ', ') 
           WITHIN GROUP (ORDER BY j.value ASC))
FROM @tbl AS t
    CROSS APPLY OPENJSON(t.jArray) AS j
GROUP BY t.ID, t.jArray
ORDER BY t.ID;

Output

+----+--------------+--------------+
| ID |    jArray    |    Result    |
+----+--------------+--------------+
|  1 | [7235, 6784] | [6784, 7235] |
|  2 | [3235, 2334] | [2334, 3235] |
|  3 | [9245, 2784] | [2784, 9245] |
|  4 | [6235, 1284] | [1284, 6235] |
+----+--------------+--------------+

ORDER BY the IN value list

You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().

Syntax will be like this:

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

Ordering by list of strings in Oracle SQL without LISTAGG

There are really two questions here:

1) How to aggregate more than 4000 characters of data

Is it even sensible to aggregate so much data and display it in a single column?

Anyway you will need some sort of large structure to display more than 4000 characters, like a CLOB for example. You could write your own aggregation method following the general guideline described in one of Tom Kyte's thread (obviously you would need to modify it so that the final output is a CLOB).

I will demonstrate a simpler method with a nested table and a custom function (works on 10g):

SQL> CREATE TYPE tab_varchar2 AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION concat_array(p tab_varchar2) RETURN CLOB IS
  2     l_result CLOB;
  3  BEGIN
  4     FOR cc IN (SELECT column_value FROM TABLE(p) ORDER BY column_value) LOOP
  5        l_result := l_result ||' '|| cc.column_value;
  6     END LOOP;
  7     return l_result;
  8  END;
  9  /

Function created.

SQL> SELECT item,
  2         concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  3    FROM data
  4   GROUP BY item;

ITEM ATTRIBUTES
1    a b c
2    a c
3    a b

2) How to sort large data

Unfotunately you can't sort by an arbitrarily large column in Oracle: there are known limitations relative to the type and the length of the sort key.

• Trying to sort with a clob will result in an ORA-00932: inconsistent datatypes: expected - got CLOB.
• Trying to sort with a key larger than the database block size (if you decide to split your large data into many VARCHAR2 for example) will yield an ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I suggest you sort by the first 4000 bytes of the attributes column:

SQL> SELECT * FROM (
  2     SELECT item,
  3            concat_array(CAST (collect(attribute) AS tab_varchar2)) attributes
  4       FROM data
  5      GROUP BY item
  6  ) order by dbms_lob.substr(attributes, 4000, 1);

ITEM ATTRIBUTES
3    a b
1    a b c
2    a c

Find List of string in List of strings simplify like in SQL

You need to solve 3 problems here:

1. Test if a string contains a given substring
2. Test the above condition for any of multiple substrings
3. Apply this to a list of strings

To test whether a given substring (ie. "One") occurs in a given string (ie. "One Two"), you could use the string.IndexOf() method:

var indexOfSubstring = "One Two".IndexOf("One");
if(indexOfSubstring >= 0){
    // substring was found in string
}

To test whether any of a list of substrings match the above condition, we can use the LINQ Any() method:

var substrings = new string[] { "One", "Two", "Three", "Four" };
substrings.Any(s => "One Two".IndexOf(s) >= 0)

And finally, to filter a whole list of strings, we can use the LINQ Where() method:

var strings = new string[] { "One Two", "Earth", "Land Four", "The One", "Hello Wolrd", "World Everest", "Then now Two" };
var substrings = new string[] { "One", "Two", "Three", "Four" };

var filteredList = strings.Where(w => substrings.Any(s => w.IndexOf(s) >= 0)).ToList();

---

If a case-insensitive comparison is desired, pass a StringComparison instance to the IndexOf() call:

var filteredList = strings.Where(w => substrings.Any(s => w.IndexOf(s, StringComparison.CurrentCultureIgnoreCase) >= 0)).ToList();

sql ORDER BY multiple values in specific order?

...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END, id

SQL - order by list order

If you need the output to appear in a particular order, then you need to specify that order, using something the server can sort. Not knowing which engine you're working against, the general scheme would be to create a temp table or use rowset constructors to pair each record ID with its desired sort order.

E.g. (SQL Server)

declare @T table (RecordID int,Position int)
insert into @T (RecordID,Position)
select 22,1 union all
select 15,2 union all
select 105,3 union all
select 1,4 union all
select 65,5 union all
select 32,6

select * from Table t inner join @T t2 on t.RecordID = t2.RecordID order by t2.Position

SQL order string as number

If possible you should change the data type of the column to a number if you only store numbers anyway.

If you can't do that then cast your column value to an integer explicitly with

select col from yourtable
order by cast(col as unsigned)

or implicitly for instance with a mathematical operation which forces a conversion to number

select col from yourtable
order by col + 0

BTW MySQL converts strings from left to right. Examples:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

---

Related Topics