Different between substr and charindex in sql ?

The substring() returns the string from the starting position however the CHARINDEX returns the substring position

CHARINDEX

CHARINDEX is another simple function that accepts two arguments. The first argument is the character you are searching for; the second is the string. It will return the first index position that the character passed into the first argument is within the string.

Now let's use our CHARINDEX function to find the position of the space in this string:

SELECT CHARINDEX(' ','Hello World');

Here's the result:

As you can see, the position of the space within "Hello World" is the 6th character. CHARINDEX can be a useful function for finding occurrences of a character within a table programmatically. I will build on this subject later on in this article.

SUBSTRING

I would consider SUBSTRING to be the most useful of all the functions mentioned today. It accepts three arguments, the string, a start position and how many characters it will "step over". Let's take a look at that illustration from earlier:

Now I'll write a simple query to show the use of SUBSTRING:

SELECT SUBSTRING('HELLO WORLD',4,5)

And now the results:

As you can see. SUBSTRING includes spaces as a position within a string. So executing this query shows a "window" of the string that has been passed to it. If we had executed the query as "SELECT SUBSTRING('HELLO WORLD',6,5)" then the results would have shown " WORL".

The SUBSTRING() function extracts the substring from the specified string based on the specified location.
The CHARINDEX() function returns the substring position inside the specified string. It works reverse to the SUBSTRING function. The substring() returns the string from the starting position however the CHARINDEX returns the substring position.
Syntax: SUBSTRING(expression, startposition, length)
             CHARINDEX(substring, inputstring)

Hi Naresh Beniwal,

• substring() returns the string from the starting position
• charindex() returns the substring position

For more details:

• SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries
  • https://www.sqlshack.com/substring-patindex-and-charindex-string-functions-in-sql-queries/

Hope, this will help you!