We help IT Professionals succeed at work.

DB2: Need to extract specific characters left of delimiter in string

Steven Ruiz
Steven Ruiz asked
on
I am using Data Studio to write a query for a DB2 AIX database.  I have a small issue I'm running into.  

I have a column that appears like this:

SBN:123456=1
SBN:1234=0
SBN:12345678=5

Open in new window


I need to extract all characters, starting from the "SBN", all the way up to, but not including, the equal sign.  The numbers after "SBN" can be of varying length, so I just need the beginning of the string up until the equal sign.  What is the easiest way to accomplish this?
Comment
Watch Question

View Solution Only

Software Developer / Database Administrator
BRONZE EXPERT
Commented: 
Select somechars,
       substr(somechars,1,locate('=', somechars)-1) as theStuff
  from deleteme;

SOMECHARS                      THESTUFF                       
------------------------------ ------------------------------ 
SBN:123456=1                   SBN:123456                     
SBN:1234=0                     SBN:1234                       
SBN:12345678=5                 SBN:12345678

Open in new window


HTH !
Steven RuizApplication Analyst

Author

Commented:
@Dave Ford, I did try something similar to this earlier (I should probably append my original comment), and then I tried your code, and I received the error "The statement was not executed because a numeric argument of a scalar function is out of range - SQLCODE 138" in both instances.  Any ideas why I'm getting that?  I isolated that piece of code from the query to try and figure it out, but I'm still getting that error using just what you posted.
Steven RuizApplication Analyst

Author

Commented:
Looks like my issue was caused by some NULLs.  Got rid of those and it works well.  Thanks!