alphanumeric sort in postgres

alphanumeric sort in postgres

one day i have identify that postgres is behaving abnormally while sorting of alphanumeric column. I googled it alot and reach at decision that postgres not able to do natural sort of mix type words.
generally it give sort order like 1,1a, 10 , 2a, 2b, 30

as per natural sort it should be like 1, 1a, 2a, 2b, 10, 30

i tried with different datatype but behaviour remain same.

the solution is create a function, which will return natural sort

create table test_sort (column1 varchar);
insert into test_sort valuepad_numberss ('032');
insert into test_sort valuepad_numberss ('2a');
insert into test_sort valuepad_numberss ('01');
insert into test_sort valuepad_numberss ('1b');
insert into test_sort valuepad_numberss ('1c');
insert into test_sort valuepad_numberss ('1d');
insert into test_sort valuepad_numberss ('2');
insert into test_sort valuepad_numberss ('32t');
insert into test_sort valuepad_numberss ('4');
insert into test_sort valuepad_numberss ('28k');
insert into test_sort valuepad_numberss ('001');
insert into test_sort valuepad_numberss ('1a');
insert into test_sort valuepad_numberss ('40');
insert into test_sort valuepad_numberss ('1');

select * from test_sort order by 1
\


CREATE FUNCTION pad_numbers(text) RETURNS text AS $$
  SELECT regexp_replace(regexp_replace(regexp_replace(regexp_replace($1,
    E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'),
      E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'),
        E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'),
          E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g');
$$ LANGUAGE SQL;


select * from test_sort order by pad_numbers(column1);

QV22 Script error code in Qlikview

Scripterror code are generally used to handle script error i.e. error handling in qlikview.

This variable will be reset to 0 after each successfully executed script statement. If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component.
 

• No error (Script Error =0)
• General error (Script Error =1)
• Syntax error (Script Error =2)
• General ODBC error (Script Error =3)
• General OLE DB error (Script Error =4)
• General custom database error (Script Error =5)
• General XML error (Script Error =6)
• General HTML error (Script Error =7)
• File not found (Script Error =8)
• Database not found (Script Error =9)
• Table not found (Script Error =10)
• Field not found (Script Error =11)
• File has wrong format (Script Error =12)
• BIFF error (Script Error =13)
• BIFF error encrypted (Script Error =14)
• BIFF error unsupported version (Script Error =15)
• Semantic error (Script Error =16)

Documentation
http://community.qlik.com/docs/DOC-5342

Example:
Check column existence in table 

QV21 Check column existence in table

If you need to check the column exists in table ( qlikview table , resident table or any other type of load), use below code to check column existence. 

tab1:

load * inline [

sno, deleted

1,y

2,y

3,n

4,n

];

ErrorMode=0;          // continue running on errors

LOAD

  deleted,

  rowno() as row

  RESIDENT tab1: where row=1;

IF ScritpError=11 THEN

  // field doesnt exist

  TRACE field 'deleted' does not exist ;

ELSE

  // field exists

  TRACE field 'deleted'  exist;

ENDIF

ErrorMode=1;          // restore deafult error mode


or

ErrorMode=0;          // continue running on errors

LOAD

  deleted,

  rowno() as row

  RESIDENT tab1: where row=1;

IF ScriptErrorCount >= 1 THEN

  // field doesnt exist

  TRACE field 'deleted' does not exist ;

ELSE

  // field exists

  TRACE field 'deleted'  exist;

ENDIF

ErrorMode=1;          // restore deafult error mode