0

How to select string between 2 specific characters from SQL Server column? I am trying to select data between the two semicolons.

I have a column A with below data:

 Gift Cards; MK-GC-001; 65gl082
 Dream Bar Ingredients; OP-STK-040; 650lp82
 Stove Logo ID; OP-STK-061; 6508po2
 Tamper Proof Sticker; OP-STK-094; GL65082
 Pick and Package; PP-001; 6508ap2

Desired output

  MK-GC-001
  OP-STK-040
  OP-STK-061
  OP-STK-094
  PP-001

I have tried the below code, I am unable to extract the desired output from last line

reverse([A]),substring(reverse(substring(reverse([A]),0, charindex(';',reverse([A]),  charindex(';',reverse([A])) + 1))),0, charindex(';',reverse([A]))+1)

Output that I get is

 MK-GC-001
 OP-STK-04
 OP-STK-06
 OP-STK-09
 PP-001; G
Dale K
  • 25,246
  • 15
  • 42
  • 71
dsayalee
  • 3
  • 2
  • 1
    First of all, don't store values like this unless you *don't* need to query them. This violates the most basic database design rule. If you only need to query them occasionally, store them as XML or JSON at least so you can use eg `JSON_VALUE` to extract values with eg `SELECT JSON_VALUE(thatField, '$[1]')` – Panagiotis Kanavos Jul 19 '23 at 19:04
  • If you can't fix the data, you can convert them to JSON by replacing `;` with `","` and surrounding the string with `'["` and `"]`, eg `select json_value('["' + replace(field,'","') + '"]','$[1]')`. This is shown in the duplicate too – Panagiotis Kanavos Jul 19 '23 at 19:08

0 Answers0