SUBSTRING & INSTR in SQL

 Problem: 

I have a case where I need to extract the 853-259020- from 853-259020-101

 Solution: 

SUBSTRING("PRTNO", 1, INSTR("PRTNO", '-', 1, 2)) AS "Part Group"

🔍 Explanation of Each Function:

1. INSTR("PRTNO", '-', 1, 2)

• This function searches for the position of the second occurrence of the hyphen - in the string "PRTNO".
• Parameters:
  • "PRTNO": The column you're searching in.
  • '-': The character you're looking for.
  • 1: Start searching from the first character.
  • 2: Find the second occurrence.
• Example: For '853-259020-101', the second hyphen is at position 11.

2. SUBSTRING("PRTNO", 1, INSTR(...))

• This extracts a substring from "PRTNO", starting at position 1 (the beginning of the string), and ending at the position of the second hyphen.
• So, it returns everything up to and including the second hyphen.

✅ Final Output:

For '853-259020-101', the result will be:

853-259020-