Database: SUBSTRING upto first occurence of charac

Question

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

Answer 1

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_bif_substr.htm

Answer 2

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)

Answer 3

SELECT SUBSTRING('Hello',0,CHARINDEX('o','Hello',0)) Would return "Hell" in SQL. I'm not sure about db2 as400, but you have tagged "SQL" as well. Hope this helps