QV23 keepchar & purgechar in qlikview

replace equivalent in qlikview:

Want to remove unwanted chars from string?
Qlikview provide us very simple function like "keepchar" and "purgechar".

Keepchar - it will return only string, which we want to keep (we need to mention list of char, that we want to keep)
purgechar - it will remove unwanted chars from string (we need to mention list to char, that we dont want to keep)


examples:
=keepchar ( 'j%4in'(fo!','abcdefghijklmnopqrstuvwxyz0123456789' ) returns 'j4info'
=PurgeChar('j%4in'(fo!', '%(!' &chr(39))

*chr(39) - is used for remove single quote from string.

ROWNUM - Limiting Results and limitations

Rownum is mostly used pseudocolumn column, used for limit result like LIMIT in other databases and also used for TOP N-query.

Some people used rownum in query and ask why it don't return exact result they want. Before using rownum we should really know the working of rownum.

WORKING
ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N. A ROWNUM value is not assigned permanently to a row. 

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row: 

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure: 

select ..., ROWNUM
  from t
 where 
 group by 
having 
 order by ;

Think of it as being processed in this order:
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.
That is why a query in the following form is almost return wrong result:(emp list is not top most)

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

LIMITATION 
ROWNUM only work with < in where clause

ROWNUM can not used for range like if you need result between some range

SOLUTION TO THIS use INLINE QUERY

TOP N result: 

below query give us list of top paid employees 

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

export import of mysql database on LINUX machine

How to take export of mysql database  on sql file and after this we are going to import this on new database on mysql (installed on linux machine).

take export of database to sql file_name
current path
"/home/singh/"
(you can choose path your own)
mysqldump -u root -p database_name > file_name.sql

import sql dbbackup to database
- create new database
    mysql - u root -p
    - mysql> create database database_name_new;
    - mysql> use database_name_new;
    - mysql> source /home/singh/file_name.sql

Search String in MYSQL

to search string from mysql database from all tables, than here we have procedure for it. Execute below two queries,
1. create table, which will store the result into it
2. execute procedure on database, from which you want to search string.


create table temp_details (t_db varchar(50), t_table varchar(50) , t_field varchar(50));

DELIMITER $$

DROP PROCEDURE IF EXISTS search_string $$

CREATE PROCEDURE search_string(in_search varchar(50))
READS SQL DATA
BEGIN
    DECLARE trunc_cmd VARCHAR(50);
    DECLARE search_string VARCHAR(250);
    DECLARE db,tbl,clmn CHAR(50);
    DECLARE done INT DEFAULT 0;
    DECLARE COUNTER INT;
    DECLARE table_cur CURSOR FOR
        SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"') ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN ('schema_name');
    
    # note change schema name to present schema name
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    
    PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
    EXECUTE trunc_cmd ;
    
    OPEN table_cur;
    table_loop:LOOP
        FETCH table_cur INTO search_string,db,tbl,clmn;
        #Executing the search
        SET @search_string = search_string;
        #SELECT search_string;
        PREPARE search_string FROM @search_string;
        EXECUTE search_string;
        SET COUNTER = @CNT_VALUE;
        #SELECT COUNTER;
        IF COUNTER>0 THEN
            # Inserting required results from search to table
            INSERT INTO temp_details VALUES(db,tbl,clmn);
        END IF;
        IF done=1 THEN
            LEAVE table_loop;
        END IF;
    END LOOP;
    
    CLOSE table_cur;
    
    #Finally Show Results
    SELECT concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details;
END $$
DELIMITER ;

call search_string('abc');

it will return some set of queries, which we can directly execute to check string respective value in table.

related link:

SEARCH STRING IN POSTGRES

http://j4info.blogspot.in/2014/10/string-search-in-database-postgres.html

 

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);