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

问题描述

我有字符串类型abc_01，abcd_01或02现在我想要子字符串_to abc_，abcd_等我使用db2 as400。请通过RIGHT或LEFT函数建议处理$ /

解决方案

使用POSITION内置函数。格式是：

  POSITION  - （ -  search-string  -  IN  -  source-string--）
  

或

  POSSTR  - （ -  source-string  - ， -  search-string--）
  

我还建议使用一个CASE结构来检查什么时候没有_或如果它在开始或结束。这是一个例子。我们假设，为了这个例子，该领域被创造性地命名为FIELD1，并且它不允许NULL值。

 CASE WHEN POSITION（'_'IN FIELD1） 1 THEN''
当位置（'_'IN FIELD1）= LENGTH（FIELD1）THEN''
 ELSE RIGHT（FIELD1，LENGTH（FIELD1）-POSITION（'_'IN FIELD1））END AS 右侧
从MYTABLE1 
  

您的问题要求使用左侧和右侧-in函数，这就是这个例子所用的。对于右侧，我建议使用SUBSTRING更容易，更可读。它将如下所示：SUBSTRING（FIELD1，POSITION（'_'IN FIELD1）+1）

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

解决方案

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

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

or

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

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

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屋！