Character sorting in Oracle string Fields

This is the SQL solution of zookeeper.

Http://blog.csdn.net/happyflystone/archive/2009/01/17/3819863.aspx

 

Reference requirements

 

Today, I chatted with Liang Weng in the group. The little guy suddenly threw out an interesting question, that is, the string Sorting Problem in the string field, such as column Col and data 'rdgs ', the output must be 'dgrs '.

 

 

--------------------------------------------------------------------------------

Oracle analysis functions support aggregation :)

 

 

 

Create Table t_test (F varchar2 (10 ));

Insert into t_test values ('sdffesa ');

Insert into t_test values ('asdflkj ');

Insert into t_test values ('ijf92 ');

Select F, max (replace (sys_connect_by_path (C, ''),'', '') F1 from (

Select F, RN, C, row_number () over (partition by f order by C) as ord from (select

F, decode (RN,

1, substr (F, 1, 1 ),

2, substr (F, 2, 1 ),

3, substr (F, 3, 1 ),

4, substr (F, 4, 1 ),

5, substr (F, 5, 1 ),

6, substr (F, 6, 1 ),

7, substr (F, 7, 1 ),

8, substr (F, 8, 1 ),

9, substr (F, 9, 1 ),

10, substr (F, 10, 1)

) As C, RN from

T_test ,(

Select level RN from dual connect by 1 = 1 and level <= 10) B

Where length (A. f)> = B. Rn ))

Start with ord = 1 connect by F = prior F and ord-1 = prior ORD

Group by F;

Drop table t_test;

/*

F F1

Ijf92 29fij

Asdflkj adfjkls

Sdffesa adeffss

*/

--------------------------------------------------------------------------------

Later, inspired by xiaoliang, I modified the method of splitting strings to remove decode.

-- Test environment

Create Table t_test (F varchar2 (10 ));

Insert into t_test values ('sdffesa ');

Insert into t_test values ('asdflkj ');

Insert into t_test values ('ijf92 ');

-- Test string splitting

Select

F, substr (F, RN, 1) as C from

T_test ,(

Select level RN from dual connect by 1 = 1 and level <= 10) B

Where length (A. f)> = B. Rn order by F;

/*

F c

Asdflkj

Asdflkj F

Asdflkj J

Asdflkj d

Asdflkj L

Asdflkj K

Asdflkj s

Ijf92 F

Ijf92 2

Ijf92 I

Ijf92 9

Ijf92 J

Sdffesa

Sdffesa s

Sdffesa E

Sdffesa F

Sdffesa d

Sdffesa s

Sdffesa F

*/

-- Test split-complete aggregate statement

Select F, max (replace (sys_connect_by_path (C, ''),'', '') F1 from (

Select F, C, row_number () over (partition by f order by C) as ord from (select

F, substr (F, RN, 1) as C from

T_test ,(

Select level RN from dual connect by 1 = 1 and level <= 10) B

Where length (A. f)> = B. Rn ))

Start with ord = 1 connect by F = prior F and ord-1 = prior ORD

Group by F;

Drop table t_test;

/*

F F1

Ijf92 29fij

Asdflkj adfjkls

Sdffesa adeffss

*/

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/jinjazz/archive/2009/01/20/3844336.aspx