0

I'm using Microsoft SQL Server and I have a description column that mentions various IDs that I want to extract. Here's what my input column looks like:

Finding
Lorem ipsum dolor sit amet, consectetur adipiscing elit, APPID-12345 sed do eiusmod tempor incididunt
Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat APPID-3782
Duis aute irure dolor in reprehenderit APPID-374 in voluptate velit esse cillum dolore eu fugiat nulla pariatur APPID-3458

Those APPIDs in italic are what I'm trying to extract. It's quite tricky because: every ID has inconsistent length, has different placements in the column, and some IDs may occur more than once in a row. Here's what I've done so far:

SELECT
Finding,
SUBSTRING(Finding,CHARINDEX('APPID',Finding,1),12) AS pos
FROM STG.Issues_Inventory
WHERE Finding LIKE '%APPID%'

But this ignores the second APPID. I'm pretty sure I'm looking at this the wrong way, so I would definitely appreciate your input/help.

Thank you!

smoothiegirl97
  • 19
  • 1
  • 5
  • When you post questions and you get error messages you really should include the error message. In this case it is obvious what the problem is. The third parameter to SUBSTRING is an int, you are passing is the value in the column which in this case is string of characters. – Sean Lange Aug 01 '22 at 18:34
  • Split the string into "words" based on a space delimiter. Then select those that start with APPID? Simply searching will find string splitters that work with your version of sql server. – SMor Aug 01 '22 at 18:36
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Aug 01 '22 at 18:47
  • What is your expected results? – Stu Aug 01 '22 at 18:53

0 Answers0