Previous Topic Next topic Print topic


SUBSTR

ANSI DB2 SQL/DS XDB
  X   X

Extracts n characters from x, beginning with the mth character (also written XMID in XDB mode). The format appears below:

SUBSTR(x, m [,n])

The parameter x is any expression that evaluates to a string. The parameter m is a positive integer indicating the offset from the beginning of the string, and the parameter n is a positive integer indicating the number of characters to extract. If two arguments are specified, the implicit length is LENGTH(x)-m+1 for fixed length fields or the larger of 0 or LENGTH(x)-m+1 for varying length fields. If x is null, the result is null.

For example, suppose the serial number for a deluxe widget is SW1234-4356, where the first two characters indicate the department that made it, the next 4 the model, and the rest the specific unit from the lot. To find the model number "1234" for deluxe widgets:

SELECT SUBSTR(serial_no, 3, 4)
  FROM products
  WHERE description = "Deluxe Widget"
Previous Topic Next topic Print topic