Remove all or some special characters from a string using regular expressions

Source:

CREATE TABLE FILENAM(FILENAME VARCHAR2(200));
INSERT INTO FILENAM VALUES('FILE1/\:');
INSERT INTO FILENAM VALUES('FILE1/?<');
INSERT INTO FILENAM VALUES('FILE1/?>');
INSERT INTO FILENAM VALUES('FILE1/?|');
INSERT INTO FILENAM VALUES('FILE1/?=<');
INSERT INTO FILENAM VALUES('FILE1/?<,');
INSERT INTO FILENAM VALUES('FILE1/?<*');
INSERT INTO FILENAM VALUES('FILE1/\:*?<>|=,');
INSERT INTO FILENAM VALUES('FILE1/\:*?<>|$=,');
COMMIT;


Query:

Output:

Identifying all the domains in the data

Input:

Output:

Identify the domain in the email id

Usually we know how to use SUBSTRING and INSTRING functions to extract a portion of a string passing the positions of a character.

Here we have a scenario , step ahead of normal scenario.

The username itself contains @symbols.It's just an assumption.

Split the comma separated values of a single column into multiple rows

I have a column and value is 1,2,3,4,5,6. Need to split this value into rows.

Note: 1,2,3,4,5,6 is a single value under column.

Find all the positions of an occurrence of a character in a string

I have an SSN# with format 123-45-6789. Here the character '-' is repeated in 4th and 7th position of a string. I need to write an SQL query to find out the positions of that characters.

DISPLAY MISSING NUMBER IN THE SEQUENCE

I have a sequence with fixed boundaries and there can be any number of misses in the sequence in a random fashion. I need to get those missed numbers.


INPUT:

OUTPUT:

DISPLAY THE MOST REPEATED NUMBER

I have a sequence of numbers repeated.I need to get the number which repeated maximum number of times.

Resetting the ROW_NUMBER

What if I have the data like this?

RESET a RANK

SQL> select * from t1;

      DEPT DATE1
---------- ---------
       100 01-JAN-13
       100 02-JAN-13
       200 03-JAN-13
       100 04-JAN-13

select dept,  
       date1,
       rank () Over (partition by dept order by date1) rnk
from t1
order by date1;

      DEPT DATE1            RNK
---------- --------- ----------
       100 01-JAN-13          1
       100 02-JAN-13          2
       200 03-JAN-13          1
       100 04-JAN-13          3

The desired output is as follows. The last rnk=1 is becuase the Jan-04 record is the first record after the change.

      DEPT DATE1            RNK
---------- --------- ----------
       100 01-JAN-13          1
       100 02-JAN-13          2
       200 03-JAN-13          1
       100 04-JAN-13          1  <<<----------

Solution:

select dept, date1,
       CASE WHEN StartFlag = 0 THEN 1
            ELSE 1+StartFlag+NVL(lag(StartFlag) over (order by date1),0)
       END as rnk
from (select t1.*,
             (case when dept = lag(dept) over (order by date1)
                   then 1
                   else 0
              end) as StartFlag
      from t1
     ) t1
order by date1;

CUBE, ROLLUP , GROUPING SETS & GROUPING_ID

The GROUPING_ID function provides an alternate and more compact way to identify subtotal rows. Passing the dimension columns as arguments, it returns a number indicating the GROUP BY level.

Generate RANDOM values

You can generate random values from DBMS_RANDOM supplied package.

Get SAMPLE rows from a table

CUBE, ROLLUP and GROUPING SETS

GROUPING SETS simplifies the management of the subtotals

CUBE, ROLLUP and GROUPING SETS

CUBE:
Super aggregation is performed for each dimension.

CUBE, ROLLUP and GROUPING SETS

The CUBE, ROLLUP and GROUPING SETS functions are used in the GROUP BY clause to generate totals and subtotals.

ROLLUP can have more than one dimension, generating grand totals and subtotals:

Calculate maximum and minimum salary without using GROUP functions

Remove the extra spaces between the words in a sentence

Complex SQL Scenario

Get the deptno from employee table that are not in dept table and deptno from dept table that are not in employee table without using SET operators.

SELECT
COALESCE(DEPT.DEPTNO,EMP.DEPTNO)
FROM
EMP
FULL OUTER JOIN
DEPT
ON EMP.DEPTNO=DEPT.DEPTNO
WHERE EMPNO IS NULL OR DNAME IS NULL

/

Complex SQL Scenario

Case1:Pick the max DATE1 if multiple distinct values.
Case2:If  no value or same value exists for DATE1 then pick maximum value from DATE2

SELECT
CLIENT_ID,
CASE 
WHEN COUNT(
DISTINCT  COALESCE(DATE1,TO_DATE('01-JAN-00','DD-MON-YY')))>1 
THEN MAX(DATE1) 
ELSE
MAX(DATE2)
END
FROM ADHOC
GROUP BY CLIENT_ID

Generate date sequence given a range

SELECT TO_DATE('01-JUN-2015') + ROWNUM - 1
FROM (
  SELECT ROWNUM FROM (
    SELECT 1 FROM DUAL
    CONNECT BY LEVEL <= (
TO_DATE('01-JUL-2015') - TO_DATE('01-JUN-2015')
                                                  )
                                                  )
           );