---
title: "DB2 Functions POSSTR Alternative to LOCATE"
description: "LOCATE and POSSTR string functions in DB2"
url: https://srinimf.com/2014/09/04/db2-functions-posstr-and-locate/
date: 2014-09-04
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2014/09/pexels-photo-10504668.jpeg?fit=1880%2C1253&ssl=1
categories: ["DB2", "SQL"]
tags: ["LOCATE function DB2", "POSSTR Function DB2"]
type: post
lang: en
---

# DB2 Functions POSSTR Alternative to LOCATE

## **POSSTR – DB2 Function**

Similar to the [LOCATE function](https://srinimf.com/2014/08/15/db2-locate-scalar-function/), but with the arguments reversed. POSTER returns the position of the first occurrence of the second argument within the first argument.

For example

```
SELECT POSSTR('DATABASE ADMINISTRATION', 'ADMIN')
FROM SYSIBM.SYSDUMMY1;
```

## **LOCATE – DB2 Function**

Returns the position of the first occurrence of the first string to the second string.

For example

```
SELECT LOCATE('I', '[CRAIG MULLINS](https://srinimf.com/2010/09/16/db2-free-certificate-from-craig-mullins/)')
FROM SYSIBM.SYSDUMMY1;
```

This SQL statement returns the value 4 because the value ‘I’ first appears in position four within the searched string.

It also appears in the 11th position, but that is of no concern to the LOCATE function. Optionally, a third argument can be supplied indicating where the search should start. For example

```
SELECT LOCATE('I', 'CRAIG MULLINS', 7)
FROM SYSIBM.SYSDUMMY1;
```

This SQL statement returns the value 11 because after position 7, the value ‘I’ first appears in the 11th position.

When the third argument is not specified, LOCATE defaults to the beginning of the second string.

**References**

- [Ref here](http://www.informit.com/articles/article.aspx?p=174097&seqNum=2)

Keep Reading

- [3 Popular Ways to Handle DB2 VARCHAR Field in COBOL](https://srinimf.com/2014/08/14/3-ways-to-handle-db2-varchar-filed-in-cobol/)DateAugust 14, 2014
- [SQL Functions and Return Code +100](https://srinimf.com/2012/06/16/sql-functions-and-return-code-100/)DateJune 16, 2012
- [DB2 LOCATE Function Advanced Examples](https://srinimf.com/2014/08/15/db2-locate-scalar-function/)DateAugust 15, 2014

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2014%2F09%2F04%2Fdb2-functions-posstr-and-locate%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2014%2F09%2Fpexels-photo-10504668.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20Functions%20POSSTR%20Alternative%20to%20LOCATE)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2014/09/04/db2-functions-posstr-and-locate/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2014/09/04/db2-functions-posstr-and-locate/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2014/09/04/db2-functions-posstr-and-locate/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2014/09/04/db2-functions-posstr-and-locate/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20Functions%20POSSTR%20Alternative%20to%20LOCATE&body=https%3A%2F%2Fsrinimf.com%2F2014%2F09%2F04%2Fdb2-functions-posstr-and-locate%2F&share=email)
-

### Like this:

Like Loading…