i have string type abc_01, abcd_01 or开发者_JAVA百科 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)
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_bif_substr.htm
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
精彩评论