How do you define ladder for the last value?? i.e. how do you get 24 in last row?? Apart from that, required query is -

SELECT COL1, NVL2(NEXT_COL1, COL1 || '--' || (NEXT_COL1 - 1), NULL) COL2
FROM (
SELECT COL1
,LEAD(COL1, 1, NULL) OVER (ORDER BY COL1) AS NEXT_COL1
FROM (SELECT LEVEL*5 COL1 FROM DUAL CONNECT BY LEVEL < 5)
)
;

And the output will be -

COL1 COL2
5 5--9
10 10--14
15 15--19
20 NULL

Thanks & Regards, Niral Soni

Format the date object as required...

function showTime(obj) {
obj.title = (new Date()).toString();
}

Thanks & Regards, Niral Soni

function splitString(myStr) {
var regex = new RegExp(/[a-z]+[0-9]*[a-z]*=/ig);
var keyPairs = myStr.match(regex).join('').split('=');
var valuePairs = myStr.split(regex);
document.write('======== Start ==========
');
document.write('INPUT: ** ' + myStr + '**
');

var map = {};
for(var i = 0; i < keyPairs.length - 1; i++) {
    map\[keyPairs\[i\]\] = valuePairs\[i+1\];
}
for(key in map) {
    document.write(key + ' = ' + map\[key\] + '  

');
}
document.write('======== End ==========

');
}

splitString('x=123y=2013/02/01z=12345p=111');
splitString('pa1n=1234h1nd=4321gr33d=6789');

Thanks & Regards, Niral Soni

Hi Robymon, The example you have given is incorrect. Adding 12 days to 20-02-2013 will not give result as 01-03-2013, but will give 04-03-2013 The solutions given by "Richard Deeming" and "Dennis E White" are correct.

Thanks & Regards, Niral Soni

I am not sure what database you are using, but below is the query which resolves your problem and that works well in Oracle 11g.

SELECT MSGNUM, LISTAGG(DESCR, ',') WITHIN GROUP (ORDER BY LVL) AS MSGDESCR
FROM (SELECT DISTINCT MSGNUM, LEVEL LVL
,SUBSTR(NVL2(MSGNUM, MSGNUM || ',', NULL)
,(CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
,INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL)
- (CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
) MSGNUM_TO_ID
FROM
-- REPLACE FOLLOWING BLOCK WITH YOUR FIRST TABLE
(SELECT SYSDATE - 1 STARTDATE, '1,2,3' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 2 STARTDATE, '4' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 3 STARTDATE, '5,6' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 4 STARTDATE, '' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 5 STARTDATE, '4,2,5' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 6 STARTDATE, '' MSGNUM FROM DUAL)
CONNECT BY INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) != 0
) T1
-- REPLACE FOLLOWING BLOCK WITH YOUR SECOND TABLE
,(SELECT 1 ID, 'ONE' DESCR FROM DUAL UNION
SELECT 2 ID, 'TWO' DESCR FROM DUAL UNION
SELECT 3 ID, 'THREE' DESCR FROM DUAL UNION
SELECT 4 ID, 'FOUR' DESCR FROM DUAL UNION
SELECT 5 ID, 'FIVE' DESCR FROM DUAL UNION
SELECT 6 ID, 'SIX' DESCR FROM DUAL) T2
WHERE T1.MSGNUM_TO_ID = T2.ID
GROUP BY MSGNUM
;

Thanks & Regards, Niral Soni

Rearrange your WHERE clause as below -

WHERE C.ROW_ID = T5.PR_EMP_ID
AND T1.ROW_ID = T4.ROW_ID(+)
AND T1.PR_POSTN_ID = T5.ROW_ID(+)
--AND T1.PR_INDUST_ID = T6.ROW_ID(+)
AND C.EMP_FLG(+) = 'Y'
AND T1.CUST_STAT_CD IN ('Active','Inactive','Pending - Workflow Wizard')
AND T1.X_CLIENT_TYPE IS NOT NULL

Thanks & Regards, Niral Soni

Try this one... Change value of MIN_DEPTH and CHAR_STR as required.

SELECT SUBSTR(A.CHAR_STR, 0, LENGTH(A.CHAR_STR) - LEVEL + 1) OUTPUT
FROM (SELECT 'HPN12345' CHAR_STR, 3 MIN_DEPTH FROM DUAL) A
CONNECT BY LENGTH(A.CHAR_STR) - LEVEL + 1 >= A.MIN_DEPTH;

Thanks & Regards, Niral Soni

I was just checking my articles average rating and number of votes received. Its showing me all ZEROs. I even checked this for other forum members, but getting same result. Is something going wrong out there ?

Thanks & Regards, Niral Soni

Just added modifiers - 'i' for ignore case and 'g' for global search

var copy = "CATCOMETACISTCASEATCORACT";
var reg = /([CAT]){3}/ig;
var sd = copy.match(reg);

Hope this may help you. Regards, Niral Soni

Thanks & Regards, Niral Soni

/************************************************
function: CompareDates
Arguments: fromdate : type 'String' : format : DD/MM/YYYY
Arguments: todate : type 'String' : format : DD/MM/YYYY
Returns: Number value
if value = 1 : fromdate comes before todate
if value = 0 : fromdate and todate are same
if value = -1 : fromdate comes after todate
*************************************************/
function CompareDates(fromdate,todate) {
// Assuming the date format = DD/MM/YYYY
// modify following block if your date format is different
// new Date(year,month,day)
frmdt = new Date(fromdate.substring(6,10),fromdate.substring(3,5)-1,fromdate.substring(0,2));
todt = new Date(todate.substring(6,10),todate.substring(3,5)-1,todate.substring(0,2));

if ( todt.getTime() > frmdt.getTime() ) {
	return 1;
}
else if( todt.getTime() == frmdt.getTime() ) {
	return 0;
}
return -1;

}

Thanks & Regards, Niral Soni