How can I parse out unwanted text data from a field

Question

I am using sequel server management studio 2014, and I am wondering how I can parse out what is extraneous data for a select query. I do not wish to modify the data, only to grab what I need from it for a SSRS data source, to automate a query. The query would look like what is listed below.

An example of a value in the comments field is listed below 'Based on PO #105680 - Thomas Test GRPO Reciept Validation query test'

Ideally I only want the value '105680' so I can use it as a lookup reference. Any help would be appreciated as always.

SELECT
   [DocEntry]
  ,[DocNum]
  ,[DocType]
  ,[CANCELED]
  ,[Comments]
  FROM [Billy].[dbo].[OPDN]

Answer 1

You can use CHARINDEX to find the PO # in the text and then SUBSTRING to parse out the number as long as the format is consistent.

;WITH TEMP AS (SELECT  'Based on PO #105680 - Thomas Test GRPO Reciept Validation query test' AS comment )

SELECT comment, 
    CASE WHEN TEMP.comment LIKE '%PO #%' THEN SUBSTRING(TEMP.comment, CHARINDEX('PO #', TEMP.comment) + 4, CHARINDEX('PO #', TEMP.comment, CHARINDEX(' ', TEMP.comment) + 1) - 4) END AS PO_NUM
FROM TEMP

Your query would be something like

SELECT
   [DocEntry]
  ,[DocNum]
  ,[DocType]
  ,[CANCELED]
  ,[Comments]
  ,CASE WHEN Comments LIKE '%PO #%' THEN SUBSTRING(Comments, CHARINDEX('PO #', Comments) + 4, CHARINDEX('PO #', Comments, CHARINDEX(' ', Comments) + 1) - 4) END AS PO_NUM
FROM [Billy].[dbo].[OPDN]