KR edition 162

 

KR-162 : SUBSTRing in DB2 - contributed by Santanu

 

Hi everyone,

 

A short and sweet KR; an interesting function in DB2.....

The SUBSTR function in DB2:

Let's suppose our DB2 table employee_t has a string field say FIRST_NAME X(30). Now what do we do if we want to get the distinct first 5 characters present in the table??

     The substring function comes to our rescue.

Syntax:    SUBSTR(string,starting-position,length)

Ex:                
                 SELECT DISTINCT SUBSTR(FIRST_NAME,1,5)

                 FROM owner.employee_t;

Now we'll get a list of distinct first 5 characters appearing in the column FIRST_NAME in the table employee_t.

Note: This function can also be used along with the HAVING clause (the HAVING clause is similar to the WHERE clause but is used when GROUP BY is used).