Use the below query in the SQL Override of SourceQualifier by replacing ‘Informatica’ with the Column name:
Before u use/read the following query, I would like to thank my colleague, PS(its the short name) who gave me this query.
select DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ a’, ‘ ‘)),1,’a',2,’aa’,3,’aaa’,4,’aaaa’,5,’aaaaa’,6,’aaaaaa’,7,’aaaaaaa’,8,’aaaaaaaa’,9,’aaaaaaaaa’,10,’aaaaaaaaaa’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ b’, ‘ ‘)),1,’b',2,’bb’,3,’bbb’,4,’bbb’,5,’bbbbb’,6,’bbbbbb’,7,’bbbbbbb’,8,’bbbbbbbb’,9,’bbbbbbbbb’,10,’bbbbbbbbbb’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ c’, ‘ ‘)),1,’c',2,’cc’,3,’ccc’,4,’cccc’,5,’ccccc’,6,’cccccc’,7,’ccccccc’,8,’cccccccc’,9,’ccccccccc’,10,’cccccccccc’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ d’, ‘ ‘)),1,’d',2,’dd’,3,’ddd’,4,’dddd’,5,’ddddd’,6,’dddddd’,7,’ddddddd’,8,’dddddddd’,9,’ddddddddd’,10,’dddddddddd’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ e’, ‘ ‘)),1,’e',2,’ee’,3,’eee’,4,’eeee’,5,’eeeee’,6,’eeeeee’,7,’eeeeeee’,8,’eeeeeeee’,9,’eeeeeeeee’,10,’eeeeeeeeee’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ f’, ‘ ‘)),1,’f',2,’ff’,3,’fff’,4,’ffff’,5,’fffff’,6,’ffffff’,7,’fffffff’,8,’ffffffff’,9,’fffffffff’,10,’ffffffffff’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ g’, ‘ ‘)),1,’g',2,’gg’,3,’ggg’,4,’gggg’,5,’ggggg’,6,’gggggg’,7,’ggggggg’,8,’gggggggg’,9,’ggggggggg’,10,’gggggggggg’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ h’, ‘ ‘)),1,’h',2,’hh’,3,’hhh’,4,’hhhh’,5,’hhhhh’,6,’hhhhhh’,7,’hhhhhhh’,8,’hhhhhhhh’,9,’hhhhhhhhh’,10,’hhhhhhhhhh’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ i’, ‘ ‘)),1,’i',2,’ii’,3,’iii’,4,’iiii’,5,’iiiii’,6,’iiiiii’,7,’iiiiiii’,8,’iiiiiiii’,9,’iiiiiiiii’,10,’iiiiiiiiii’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ j’, ‘ ‘)),1,’j',2,’jj’,3,’jjj’,4,’jjjj’,5,’jjjjj’,6,’jjjjjj’,7,’jjjjjjj’,8,’jjjjjjjj’,9,’jjjjjjjjj’,10,’jjjjjjjjjj’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ k’, ‘ ‘)),1,’k',2,’kk’,3,’kkk’,4,’kkkk’,5,’kkkkk’,6,’kkkkkk’,7,’kkkkkkk’,8,’kkkkkkkk’,9,’kkkkkkkkk’,10,’kkkkkkkkkk’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ l’, ‘ ‘)),1,’l',2,’ll’,3,’lll’,4,’llll’,5,’lllll’,6,’llllll’,7,’lllllll’,8,’llllllll’,9,’lllllllll’,10,’llllllllll’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ m’, ‘ ‘)),1,’m',2,’mm’,3,’mmm’,4,’mmmm’,5,’mmmmm’,6,’mmmmmm’,7,’mmmmmmm’,8,’mmmmmmmm’,9,’mmmmmmmmm’,10,’mmmmmmmmmm’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ n’, ‘ ‘)),1,’n',2,’nn’,3,’nnn’,4,’nnnn’,5,’nnnnn’,6,’nnnnnn’,7,’nnnnnnn’,8,’nnnnnnnn’,9,’nnnnnnnnn’,10,’nnnnnnnnnn’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ o’, ‘ ‘)),1,’o',2,’oo’,3,’ooo’,4,’oooo’,5,’ooooo’,6,’oooooo’,7,’ooooooo’,8,’oooooooo’,9,’ooooooooo’,10,’oooooooooo’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ p’, ‘ ‘)),1,’p',2,’pp’,3,’ppp’,4,’pppp’,5,’ppppp’,6,’pppppp’,7,’ppppppp’,8,’pppppppp’,9,’ppppppppp’,10,’pppppppppp’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ q’, ‘ ‘)),1,’q',2,’qq’,3,’qqq’,4,’qqqq’,5,’qqqqq’,6,’qqqqqq’,7,’qqqqqqq’,8,’qqqqqqqq’,9,’qqqqqqqqq’,10,’qqqqqqqqqq’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ r’, ‘ ‘)),1,’r',2,’rr’,3,’rrr’,4,’rrrr’,5,’rrrrr’,6,’rrrrrr’,7,’rrrrrrr’,8,’rrrrrrrr’,9,’rrrrrrrrr’,10,’rrrrrrrrrr’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ s’, ‘ ‘)),1,’s’,2,’ss’,3,’sss’,4,’ssss’,5,’sssss’,6,’ssssss’,7,’sssssss’,8,’ssssssss’,9,’sssssssss’,10,’ssssssssss’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ t’, ‘ ‘)),1,’t',2,’tt’,3,’ttt’,4,’tttt’,5,’ttttt’,6,’tttttt’,7,’ttttttt’,8,’tttttttt’,9,’ttttttttt’,10,’tttttttttt’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ u’, ‘ ‘)),1,’u',2,’uu’,3,’uuu’,4,’uuuu’,5,’uuuuu’,6,’uuuuuu’,7,’uuuuuuu’,8,’uuuuuuuu’,9,’uuuuuuuuu’,10,’uuuuuuuuuu’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ v’, ‘ ‘)),1,’v',2,’vv’,3,’vvv’,4,’vvvv’,5,’vvvvv’,6,’vvvvvv’,7,’vvvvvvv’,8,’vvvvvvvv’,9,’vvvvvvvvv’,10,’vvvvvvvvvv’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ w’, ‘ ‘)),1,’w',2,’ww’,3,’www’,4,’wwww’,5,’wwwww’,6,’wwwwww’,7,’wwwwwww’,8,’wwwwwwww’,9,’wwwwwwwww’,10,’wwwwwwwwww’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ x’, ‘ ‘)),1,’x',2,’xx’,3,’xxx’,4,’xxxx’,5,’xxxxx’,6,’xxxxxx’,7,’xxxxxxx’,8,’xxxxxxxx’,9,’xxxxxxxxx’,10,’xxxxxxxxxx’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ y’, ‘ ‘)),1,’y',2,’yy’,3,’yyy’,4,’yyyy’,5,’yyyyy’,6,’yyyyyy’,7,’yyyyyyy’,8,’yyyyyyyy’,9,’yyyyyyyyy’,10,’yyyyyyyyyy’)||DECODE(length (lower(‘Informatica’))-length(translate(lower(‘Informatica’), ‘ z’, ‘ ‘)),1,’z',2,’zz’,3,’zzz’,4,’zzzz’,5,’zzzzz’,6,’zzzzzz’,7,’zzzzzzz’,8,’zzzzzzzz’,9,’zzzzzzzzz’,10,’zzzzzzzzzz’)from dual
