CODEWITHSUNDEEP
sqloracle-database
Shubham Shingate
Shubham Shingate

Reputation: 55

How to select 1st half part of pipe separated data

Data in each record of a column named REQUEST_IP_ADDR is as below '10.247.32.44 | 10.247.32.44'. How do i select only 1st part that is 10.247.32.44 ?

--Below is the select query I am trying to run

SELECT DISTINCT MSG_TYPE_CD, SRC, SRC_IP from MESSAGE_LOG order by MSG_TYPE_CD;

--My table looks as below 

MSG_TYPE_CD   SRC              SRC_IP
KB0192        ZOHO         10.247.32.44 | 10.247.32.44
KB0192        ZOHO         10.247.32.45 | 10.247.32.45
KB0192        ZOHO         127.0.0.1 | 10.240.20.137
KB0192        ZOHO         127.0.0.1 | 10.240.20.138
KB0196        GUPSHUP          10.240.20.59 | 10.10.1.19

I want select only 1st part of data which is before the pipe

Upvotes: 1

Views: 134

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Using the base string functions we can try:

SELECT
    SRC_IP,
    SUBSTR(SRC_IP, 1, INSTR(SRC_IP, '|') - 2) AS first_ip
FROM MESSAGE_LOG
ORDER BY
    MSG_TYPE_CD;

enter image description here

Demo

The logic behind the first query is that we find the position of the pipe | using INSTR. Then, we take the substring from the first character until two characters before the pipe (to leave out both the pipe and the space that precedes it).

A very slick answer using REGEXP_SUBSTR:

SELECT
    SRC_IP,
    REGEXP_SUBSTR(SRC_IP, '^[^ |]+') AS first_ip
FROM MESSAGE_LOG
ORDER BY
    MSG_TYPE_CD;

Demo

The regex pattern used here is:

^[^ |]+

This says to take any character from the start of the SRC_IP column which is not space or pipe |. This means take the first IP address.

Upvotes: 3

Related Questions