数据库:SUBSTRING 直到第一次出现的字符 [英] Database: SUBSTRING upto first occurence of character

问题描述

我有字符串类型 abc_01、abcd_01 或 02 现在我想要子字符串到 _ 即 abc_、abcd_ 等.我正在使用 db2 as400.请建议通过 RIGHT 或 LEFT 函数进行处理

i have string type abc_01, abcd_01 or 02 now i want the substring upto _ ie abc_,abcd_ etc. I am using db2 as400 .Please suggest the processing through RIGHT or LEFT function

推荐答案

使用 POSITION 内置函数.格式为:

Use the POSITION built-in function. The format is either:

POSITION--(--search-string--IN--source-string--)

或

POSSTR--(--source-string--,--search-string--)

我还建议使用 CASE 结构来检查何时没有 _ 或者它是在开头还是结尾.这是一个例子.为了这个例子，我们假设有问题的字段被创造性地命名为 FIELD1，并且它不允许 NULL 值.

I also suggest using a CASE structure to check for when there is no _ or if it's at the beginning or end. Here is an example. We'll assume, for the sake of the example that the field in question is creatively named FIELD1 and that it does not allow NULL values.

SELECT 
  CASE WHEN POSITION('_' IN FIELD1) = 0 THEN FIELD1
       WHEN POSITION('_' IN FIELD1) = 1 THEN ''
       ELSE LEFT(FIELD1, POSITION('_' IN FIELD1)-1) END AS "Left Side",
  CASE WHEN POSITION('_' IN FIELD1) < 1 THEN ''
       WHEN POSITION('_' IN FIELD1) = LENGTH(FIELD1) THEN ''
       ELSE RIGHT(FIELD1, LENGTH(FIELD1)-POSITION('_' IN FIELD1)) END AS "Right Side" 
FROM MYTABLE1

您的问题要求使用 LEFT 和 RIGHT 内置函数，这就是示例所使用的.对于右边，我建议使用 SUBSTRING 更容易，更易读.它看起来像这样:SUBSTRING(FIELD1,POSITION('_' IN FIELD1)+1)

Your question requested the use of the LEFT and RIGHT built-in functions, so that's what the example uses. For the right side, I suggest that using SUBSTRING is easier and more readable. It would look like this: SUBSTRING(FIELD1,POSITION('_' IN FIELD1)+1)

这篇关于数据库:SUBSTRING 直到第一次出现的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！