CODEWITHSUNDEEP
sqloracle-database
MatteoS
MatteoS

Reputation: 95

Oracle select value upto first space or end of line

I need to run a weekly extract and update a lookup columns value from an external database. The issue I am facing is that my lookup column x_ids, contains string values of variable length and patterns i.e.

Lookup Table

x_ids
------
CHE00r
NWA048
HAM54O1A
STR191O1C

My problem is that some of the values selected for update also include a space and brackets after the value. i.e. above STR191O1C could reflect STR191O1C (250)

I have tried the following select for my update:-

select substr(b.x_ids,1,instr(b.x_ids,' ',1,1) - 1)
from lookup_tab a, external_tab b
where a.site_id = B.SITE_ID
and a.zone_id = b.zone_id

This removes the bracketed number and provides the value I'm looking for but also returns no value for the other lines that do not have brackets or spaces. I need to return all values up to end of line if there are no spaces or up to the space if it exists.

Upvotes: 1

Views: 3711

Answers (2)

MT0
MT0

Reputation: 168041

You can do it using regular expressions:

SELECT REGEXP_SUBSTR( b.x_ids, '^(.*?)($| )', 1, 1, NULL, 1 )
FROM   lookup_tab a
       JOIN external_tab b
       ON   a.site_id = B.SITE_ID and a.zone_id = b.zone_id

Or using a CASE statement:

SELECT CASE WHEN INSTR( b.x_ids, ' ' ) = 0 THEN b.x_ids
            ELSE SUBSTR( b.x_ids, 1, INSTR( b.x_ids, ' ' ) - 1 )
            END
FROM   lookup_tab a
       JOIN external_tab b
       ON   a.site_id = B.SITE_ID and a.zone_id = b.zone_id

Or:

SELECT SUBSTR(
         b.x_ids,
         1,
         CASE WHEN INSTR( b.x_ids, ' ' ) > 0 THEN INSTR( b.x_ids, ' ' ) - 1 END
       )
FROM   lookup_tab a
       JOIN external_tab b
       ON   a.site_id = B.SITE_ID and a.zone_id = b.zone_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can readily tweak your logic to handle this by adding a space to the string before search for a space:

select substr(b.x_ids, 1, instr(b.x_ids || ' ', ' ', 1, 1) - 1)
from lookup_tab a join
     external_tab b
     on a.site_id = B.SITE_ID and a.zone_id = b.zone_id

Upvotes: 4

Related Questions