Comma-separated string to tabular format

 

Comma-separated string to tabular data

Sometimes we need to extract individual data from the comma-separated strings. We can use the following query to do that in oracle.


SELECT REGEXP_SUBSTR('my comma separated string', '[^,]', 1, LEVEL) TYPE, LEVEL

FROM DUAL

CONNECT BY LEVEL <= REGEXP_COUNT('my  comma separated string','[^,]') ;

Comments

Post a Comment

Popular posts from this blog

Build shared library of ffmpeg with x264 to use in visual studio win32 project

After few painful failure attempts at last i was able to build shared library which can be used in visual studio win32 project. Here is the process which i have gone through. Hope this will be helpful. I don't want any body to face the same failure moments.. Environment settings Download msys2 32 bit form this link http://msys2.github.io and install it.(in my case installed " msys2-i686-20160205.exe " in directory is "c:\msys32" ). Run it and run below commands update-core ( Update the system packages with) close MSYS2, run it again and run below commands pacman -Sy pacman -Su  (sometimes this command creates error then you have uninstall msys2 and install again. In that case you can ignore this command) pacman -S pkg-config make diffutils  go to C:\msys32\usr\bin and rename "link.exe" to "link_backup.exe". Because we will not use this link. we will use visual studio link. Download yasm win32 ".exe...
Read more

How to Build WPF project with Setup file (Installer Project)

Image
Firstly create a WPF project say "SampleWpfApplication". Here I'm going to use Visual Studio 2013. Created a project named SampleWpfApplication You can add your necessary runtime projects' library dll into your project reference if you want. Here I included a sample runtime project's library into my project. Create Installer Project Now its time to add our installer project. By default in your visual studio you will not see "Visual Studio Installer". If you don't see then you have to download it from visual studio installer project for 2013 and install it. So next You have to create Setup project. Your project is added into your solution. Modify Installer Project Go to view File System of the installer project. Then you'll see  Add "Program Files Folder" Add Project Output 1# Firstly you have to add project output into "Application Folder" ...
Read more

Trigger for Audit Trail in Oracle

 For the Audit trail of a transactional or master data table, we can use the following format to trace the differences.  First, create a table for storing audit data named as < SCHEMA_NAME>.< TABLE_NAME>_AUDIT CREATE TABLE  < SCHEMA_NAME>.< TABLE_NAME>_AUDIT  (           COLUMN_NAME TYPE,           ACTION CHAR(1),           ACTION_DATE DATE,           ACTION_USER_ID NUMBER(18),           CLIENT_DEVICE VARCHAR2(100),            CLIENT_OS_USER VARCHAR2(100) ); Then, create a trigger named as  < TABLE_NAME>_TRIGGER CREATE OR REPLACE TRIGGER  < TABLE_NAME>_TRIGGER AFTER INSERT OR UPDATE OR   DELETE   ON < TABLE_NAME> FOR EACH ROW DECLARE          V_USER_ID NUMBER(9); ...
Read more