String Concat function-Oracle ( similar usage to SUM)

SELECT deptno, wm_concat(ename) AS employees

FROM   emp
GROUP BY deptno;

Result -----> 

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER


For Oracle 9 --> as wm_concat() function is not available use following custom function. 
CREATE OR REPLACE FUNCTION get_it (invoice in VARCHAR2)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT x AS itemName FROM invoice_breakdown where invoice_no = invoice /*query returning multiple rows*/) LOOP
    l_text := l_text || ',' || cur_rec.itemName;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS

Share Article : String Concat function-Oracle ( similar usage to SUM)

String Concat function-Oracle ( similar usage to SUM)

Check if String Contains a certain Substring

String string = "Madam, I am Adam"; 

// Starts with 
boolean b = string.startsWith("Mad"); 
// true 

// Ends with 
b = string.endsWith("dam"); 
// true 

// Anywhere 
b = string.indexOf("I am") > 0; 
// true 


// To ignore case, regular expressions must be used 
// Starts with 
b = string.matches("(?i)mad.*"); 

// Ends with 
b = string.matches("(?i).*adam"); 

// Anywhere 
b = string.matches("(?i).*i am.*");


//
.replaceAll("\\W", ""); replace all non-alphanumerics

Share Article : Check if String Contains a certain Substring

Check if String Contains a certain Substring

Auto Incrementing primary key with Oracle

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Table created.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Sequence created.

Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;

Trigger created.

Share Article : Auto Incrementing primary key with Oracle

Auto Incrementing primary key with Oracle

JSTL [S]crap

Iterating ArrayList of Arrays



<c:foreach items="${list}" var="innerList">
<c:foreach items="${innerList}" var="item">
<c:out value="${item}"></c:out>|

</c:foreach> <br>

</c:foreach>

Share Article : JSTL [S]crap

JSTL [S]crap