Select substring up to first \r\n

Hi

Can anybody help me with this? I have written the below query which works fine

SELECT CONVERT(varchar,DATEADD(Day, 7, b.[Delivery_Date]),6) AS ‘Original RFC’
,CONVERT(varchar, b.[Requested_Date],6) AS ‘Current RFC’
,DATEDIFF(day, DATEADD(Day, 7, b.[Delivery_Date]), b.[Requested_Date]) AS ‘Gap’
,DATEPART(iso_week, b.[Requested_Date]) AS ‘Collection Week’
,a.[WO_No] AS ‘WO No’
,c.[Part_No] AS ‘Spec’
,a.[UDF8__UDData_ID] AS ‘Bay_id’
,CONVERT(varchar, a.[Estimated_Complete_Date],6) AS ‘Build Start’
,CONVERT(varchar, a.[Date_Required],6) AS ‘Electrics Start’
,CONVERT(varchar, b.[Requested_Date],6) AS ‘QC Complete’
,a.[UDF1__UDData_ID] AS ‘Status_id’
,SUBSTRING(a.[Extra_Instructions_Non_RTF],1,150) AS ‘WO Notes’
FROM [Clark Live].[dbo].[WORK_ORDERS] a
LEFT JOIN [Clark Live].[dbo].[SO_Delivery_Schedule] b
ON a.[SO_Delivery_Schedule_id] = b.[SO_Delivery_Schedule_ID]
LEFT JOIN [Clark Live].[dbo].[Part] c
ON a.[Part_ID__Part_id] = c.[Part_Id]
WHERE a.[Status__Wo_Status_Id] < 5
AND a.[WO_No] IS NOT NULL
AND b.[Requested_Date] IS NOT NULL

          AND (c.[Part_No] Like 'GM0%'
          OR c.[Part_No] Like 'TAS%'
          OR c.[Part_No] Like 'spec%'
          OR c.[Part_No] Like 'Adapt%'
          OR c.[Part_No] Like 'uni%'
          OR c.[Part_No] Like 'DD0%'
          OR c.[Part_No] Like '%IVA%'
          OR c.[Part_No] Like '%VES%')
          AND c.[Part_No]NOT Like '%eng%'
          AND c.[Part_No]NOT Like '%TIM%'
          AND c.[Part_No]NOT Like '%ALI%'
          AND c.[Part_No]NOT Like '%soft%'
          ORDER BY b.[Requested_Date]

But on line 12 where I create a substring, instead of just returning the first 150 characters. I would like to select up to the first line break (\r\n in the database I am querying). Is this possible and if so how can I go about it?


This image shows an example of the result of the current query. But I want to select up to the first \r\n and return only “17/11/2021 : 07:18:01 : Brendan Jones : missing mess unit (waiting on material) go to parts”

Hello @megawhiz24365 ,

Generally, you would use some string function to find the starting position of those characters in place of where you have the value 150. The type of string function would depend on the database you are using.