DB2 SQL Anything left of a /

db2 substring
db2 substring from right
db2 substring after character
db2 locate
how to find string length in db2
db2 instr example
substring in as400 sql
delimiter db2

I've been working on this for days and can't seem to work it out. Basically I need return digits from a field before there is a forward slash. e.g. if the field was 1234/TEXT I want to return 1234. I can't just use left fieldname 4 as the digits vary in left e.g. 12345/TEXT, so it needs to be anything left of the forward slash. Now in the World of MS Access, it is something like this - and it works

Left(TABLE!FIELD,InStr(1,TABLE!FIELD,"/")-1)

However, how do I convert this to be used in an IBM\DB2 system? The DB2 SQL seems somewhat different to 'normal' SQL.

Thanks!

Rather than INSTR, maybe LOCATE

LOCATE(char, string)

char is the search term string is the string being searched

Db2 LEFT() Function By Examples, Db2 LEFT() function returns a string that consists of a specified number of leftmost characters of a string. Db2 LEFT() function returns a substring that consists of a specified number of leftmost characters from a string. Here is the syntax of the LEFT() function: LEFT(string, length); In this syntax: First, specify the source string ( string) from which to extract the substring. Second, specify the number of leftmost characters ( length) to be extracted.

You can achieve this by combining LOCATE with SUBSTR;

Locate information

Substring information

Cheat sheet (for this example);

SUBSTRING('FIELD','START POSITION', 'LENGTH')
LOCATE('SEARCH STRING', 'SOURCE STRING') 

SUBSTRING lets you retrieve specific characters from a string, i.e.;

AFIELD = 'Hello'
SUBSTRING(AFIELD,4,2)

Result = 'lo' (position 4 and 5 of Hello)

LOCATE returns the position of the first character of the search string it finds as a number, i.e.;

AFIELD = 'Hello'
LOCATE('ello', AFIELD)

Result = 2 (it starts at position 2)

So you can combine these to do what you want, example;

XTABLE has 1 column called ACOL with the following values in it;

123467/ABCD 
1321/ABDD   
1123467/ABCD

To just retrieve the numbers;

SELECT SUBSTRING(ACOL,1, LOCATE('/',ACOL)-1)
FROM XRDK/XTABLE  

Result;

123467
1321
1123467

What are we doing?

SUBSTRING(
ACOL,
1, 
LOCATE('/',ACOL)-1
)

SUBSTRING(
Field ACOL, 
Starting at position 1, 
Length; using locate set this to where I find a '/' and subtract 1 from the 
resulting postion (without the -1 you'd have the / on the end)
)

LEFT (DB2 SQL), The LEFT function returns a string that consists of the specified number of leftmost bytes of the specified string units. Character string: Read syntax diagram​  Although there are many types of software testing (unit, integration, system, regression, etc.) and all of them can be shifted left to varying degrees, developing applications that use Db2 require SQL performance testing. Db2 SQL is very flexible, and there are multiple ways to write SQL queries to achieve the same results.

Try this

SELECT SUBSTRING(CAST (ROUND(COLUMN,2) AS DECIMAL(6,2)), 0, locate('/',CAST (ROUND(COLUMN,2) AS DECIMAL(6,2))))
FROM TABLE

DB2 10 - DB2 SQL - LOCATE_IN_STRING, The LOCATE_IN_STRING function returns the starting position of a string (called the search-string) within another string (called the source-string). Introduction to Db2 LEFT JOIN clause. The LEFT JOIN clause is one of the joins that allows you to query data from two or more tables. Suppose, you have two tables: T1 and T2, called the left and the right tables respectively. The LEFT JOIN clause selects data starting from the left table (T1). It compares each row in the left table with every row in the right table.

RIGHT scalar function, If OCTETS is specified and the result is graphic data, the value must be an even number between 0 and twice the length attribute of string-expression (SQLSTATE​  DB2 can execute this query as if the view V1 was generated with a left outer join so that the query runs more efficiently. Removal of unneeded tables in left outer joins. When an SQL statement contains a left outer join, but does not select any columns from the right side of the join, DB2 can remove the join from the statement.

DB2 SUBSTR (Substring) Function, The DB2 SUBSTR function takes two arguments (source and start location) If the start location is out of range it will return a SQLSTATE 22011. the source string is effectively padded on the right with the necessary number  DB2 SQL Anything left of a / Ask Question Asked 5 years, 6 months ago. Active 8 months ago. Viewed 1k times 0. I've been working on this for days and can't seem to

DB2 SQL Tuning Tips for z/OS Developers: DB2 SQL Tunin Tips Devel, DB2 SQL Tunin Tips Devel Tony Andrews D.DEPTNAME, D.MGRNO, E.​LASTNAME FROM DEPT D LEFT JOIN EMP E ON D.MGRNO = E.EMPNO Example 2:  LEFT (Transact-SQL) LEFT (Transact-SQL) 03/13/2017; 2 minuti per la lettura; In questo articolo. SI APPLICA A: SQL Server Database SQL di Azure Azure Synapse Analytics (SQL DW) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Comments
  • You're calling MS Access "normal SQL"? In normal SQL you'd reference a table column as TABLENAME.COLUMNNAME, not TABLE!FIELD.
  • The INSTR function is available in recent DB2 versions.
  • Just noticed that this was asked in 2014, oops - hope you worked it out in the end.