Regular Expressions REGEXP_LIKE Retrieving just the Numbers alone from the Field which stores alphanumeric values.
Code:
WITH REG_EXP AS (
SELECT 'ABC' AS FIELD FROM DUAL UNION ALL SELECT 'ABC123' FROM DUAL UNION ALL SELECT '123' FROM DUAL
)
SELECT * FROM REG_EXP WHERE REGEXP_LIKE(FIELD,'^[0-9]+$');
Retrieving just the characters alone from the Field which stores alphanumeric values.
Code:
WITH REG_EXP AS (
SELECT 'ABC' AS FIELD FROM DUAL UNION ALL SELECT 'ABC123' FROM DUAL UNION ALL SELECT '123' FROM DUAL
)
SELECT * FROM REG_EXP WHERE REGEXP_LIKE(FIELD,'^[A-Z]+$');
REGEXP_INSTR Retrieving the position of the special characters from the Field. Code:
WITH REG_EXP AS (
SELECT 'It is you?' AS FIELD FROM DUAL UNION ALL SELECT 'I not expect this !!!' FROM DUAL UNION ALL SELECT 'May be?' FROM DUAL
)
SELECT FIELD,REGEXP_INSTR(FIELD, '\!!!') AS POSITION FROM REG_EXP;
Output: Code:
FIELD POSITION
--------------------- ----------
It is you? 0
I not expect this !!! 19
May be? 0
3 rows selected.
Retrieving the position of the special characters from the Field. Code:
WITH REG_EXP AS (
SELECT 'It is you?' AS FIELD FROM DUAL UNION ALL SELECT 'I not expect this !!!' FROM DUAL UNION ALL SELECT 'May be?' FROM DUAL
)
SELECT FIELD,REGEXP_INSTR(FIELD, '\?')AS POSITION FROM REG_EXP;
Output: Code:
FIELD POSITION
--------------------- ----------
It is you? 10
I not expect this !!! 0
May be? 7
3 rows selected.
And still many more......