---
title: "SQL Query: How to Use LOCATE and Substring Correctly"
description: "Here are SQL queries to use the SUBSTRING function along with LOCATE function. LOCATE gives position, and SUBSTRING uses that for the start position."
url: https://srinimf.com/2021/04/18/sql-query-how-to-use-locate-and-substring-correctly/
date: 2021-04-18
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2021/04/pexels-photo-207489.jpeg?fit=1880%2C1058&ssl=1
categories: ["SQL"]
tags: ["SQL Substring Function"]
type: post
lang: en
---

# SQL Query: How to Use LOCATE and Substring Correctly

Here is an SQL Query to use both LOCATE and SUBSTRING functions. Sometimes the value in the main string is unknown to the programmer, and he has to delimit ‘,’ or ‘space’ from it. For that matter, you need to use both functions.

## 1. How to Use [LOCATE Function](https://srinimf.com/2014/08/15/db2-locate-scalar-function/)

SQL Query to get [starting position of search-string using](https://srinimf.com/2015/07/17/mainframe-db2-locate-built-in-function/)LOCATE function.

```
SELECT RECEIVED, SUBJECT, LOCATE('GOOD', NOTE_TEXT)
FROM SRINI_LOC_TABLE
WHERE LOCATE('GOOD', NOTE_TEXT) <> 0;
```

![](https://dotcompatterns.files.wordpress.com/2020/08/dots_bg.png)

Welcome to

## Srinimf

This is my little corner of the Internet, where I talk about solutions to developers’ day-to-day problems.

*New to the site? **[Start here](https://srinimf.com)***

![](https://dotcompatterns.files.wordpress.com/2020/10/sai-de-silva-4-gfgb12hfa-unsplash-2-1.jpg?w=1024&resize=500%2C500)

## 2. How to Use both LOCATE and SUBSTRING Functions

First it gets starting position for search string using LOCATE. Later SUBSTRING function gets the string you want.

```
SELECT RECEIVED, SUBJECT, SUBSTRING(NOTE_TEXT,LOCATE('GOOD', NOTE_TEXT),4)
FROM SRINI_LOC_SUB_TABLE
WHERE LOCATE('GOOD', NOTE_TEXT) <> 0;
Result:
GOOD
```

Here is another way [you can get Substring using LEFT or RIGHT functions](https://srinimf.com/2020/04/05/sql-right-left-functions-top-ideas-to-extract-substring/), and with specified length.

**TIP:** In both LOCATE and SUBSTRING the count starts from ‘1’, and backward count starts from -1. This will resolve all Substring problems.

## 3. Explanation for Locate and SUBSTRING Functions

1. LOCATE function takes two parameters, one is ‘text you need to find’ and original string column name. You can find this in second query.
2. Substring function need three parameters as input – Original string column name, start position and length)
3. In the second query, NOTE_TEXT is column name. LOCATE function gives starting position value. The, the GOOD length is ‘4’. So, the final result from second query is ‘GOOD’.

**You may also Like:** [How to Use LEFT and RIGHT String Functions](https://srinimf.com/2020/04/05/sql-right-left-functions-top-ideas-to-extract-substring/)

### 3a. Syntax for LOCATE Function

```
SELECT ABC LOCATE(',', MY_TEXT)
FROM TEST_TABLE;
The result is index of first occurrence of ','.
```

### 3b. Syntax for SUBSTRING Function Left to Right

```
SELECT ABC SUBSTRING(MY_TEXT, 1, 4) 
FROM TEST_TABLE; 
The result is substring from position 1 and length 4.
```

### 3c. Syntax for SUBSTRING Function Right to Left

```
SELECT ABC SUBSTRING(MY_TEXT, -2, 3)  
FROM TEST_TABLE;  
The result is substring from position -2 and length 3.
```

Keep Reading

[![SQL Quiz in Three Minutes Prove Your Knowledge](https://i0.wp.com/srinimf.com/wp-content/uploads/2017/11/3-minutes-sql-quiz.jpg?fit=600%2C450&ssl=1)](https://srinimf.com/2017/11/06/three-minute-sql-quiz-prove-your-knowledge/)

### [SQL Quiz in Three Minutes Prove Your Knowledge](https://srinimf.com/2017/11/06/three-minute-sql-quiz-prove-your-knowledge/)

[![Srini&apos;s avatar](https://secure.gravatar.com/avatar/f10b4bafeeb96dce9806c5a3c6cde6ea1e2c014ef273120a4bf2c6d5f975f01c?s=48&r=g)](https://srinimf.com/author/moonrobot/)
by [Srini](https://srinimf.com/author/moonrobot/)
November 6, 2017

[![Here’s a Quiz on SQL Functions](https://i0.wp.com/srinimf.com/wp-content/uploads/2017/06/quiz-on-sql-functios.jpg?fit=600%2C450&ssl=1)](https://srinimf.com/2017/06/17/quick-sql-quiz-that-increases-your-chance-to-succeed/)

### [Here’s a Quiz on SQL Functions](https://srinimf.com/2017/06/17/quick-sql-quiz-that-increases-your-chance-to-succeed/)

[![Srini&apos;s avatar](https://secure.gravatar.com/avatar/f10b4bafeeb96dce9806c5a3c6cde6ea1e2c014ef273120a4bf2c6d5f975f01c?s=48&r=g)](https://srinimf.com/author/moonrobot/)
by [Srini](https://srinimf.com/author/moonrobot/)
June 17, 2017

[![SQL Quiz: Useful for Informatica Projects](https://i0.wp.com/srinimf.com/wp-content/uploads/2013/12/quiz-sql.jpg?fit=600%2C450&ssl=1)](https://srinimf.com/2013/12/21/sql-quiz-for-mainframe-and-informatica-free/)

### [SQL Quiz: Useful for Informatica Projects](https://srinimf.com/2013/12/21/sql-quiz-for-mainframe-and-informatica-free/)

[![Srini&apos;s avatar](https://secure.gravatar.com/avatar/f10b4bafeeb96dce9806c5a3c6cde6ea1e2c014ef273120a4bf2c6d5f975f01c?s=48&r=g)](https://srinimf.com/author/moonrobot/)
by [Srini](https://srinimf.com/author/moonrobot/)
December 21, 2013

# How to Fix Python Syntax Errors

Tips and ideas for developers.

[Checkout](https://srinimf.com/2018/02/06/python-syntax-error-tips-you-need-to-resolve/)

[Pre-order](https://srinimf.com/contact-us/)

### 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%2F2021%2F04%2F18%2Fsql-query-how-to-use-locate-and-substring-correctly%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2021%2F04%2Fpexels-photo-207489.jpeg%3Ffit%3D1200%252C675%26ssl%3D1&description=SQL%20Query%3A%20How%20to%20Use%20LOCATE%20and%20Substring%20Correctly)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2021/04/18/sql-query-how-to-use-locate-and-substring-correctly/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2021/04/18/sql-query-how-to-use-locate-and-substring-correctly/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2021/04/18/sql-query-how-to-use-locate-and-substring-correctly/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2021/04/18/sql-query-how-to-use-locate-and-substring-correctly/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL%20Query%3A%20How%20to%20Use%20LOCATE%20and%20Substring%20Correctly&body=https%3A%2F%2Fsrinimf.com%2F2021%2F04%2F18%2Fsql-query-how-to-use-locate-and-substring-correctly%2F&share=email)
-

### Like this:

Like Loading…