Oracle选择直到第一个空格或行尾的值 [英] Oracle select value upto first space or end of line

问题描述

我需要每周进行一次提取，并从外部数据库更新查找列的值.我面临的问题是我的查询列x_ids包含可变长度和模式的字符串值，即

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.

x_ids
------
CHE00r
NWA048
HAM54O1A
STR191O1C

我的问题是，一些选择用于更新的值在该值之后还包含一个空格和括号.即高于STR191O1C可能反映STR191O1C(250)

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.

推荐答案

通过在搜索空格之前在字符串中添加一个空格，您可以轻松地调整逻辑来处理此问题:

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

这篇关于Oracle选择直到第一个空格或行尾的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！