Search code examples
sqlstringperformancesql-server-2008processing-efficiency

How to select specific text from the string in efficient way using SQL

Below is my query. It is giving me correct output but I need to run it efficiently as it is used for 500k records.

 DECLARE @DESC_MESSAGE VARCHAR(5000)
 SET @DESC_MESSAGE = '12345 VENKAT was entered ODC ABCD-3'
 SELECT REPLACE(@DESC_MESSAGE,SUBSTRING(@DESC_MESSAGE,1,CHARINDEX('was',@DESC_MESSAGE,3)-1),'')

I just want to retrieve text after 'was' which can change depending on condition. for ex. text can be like '112233 XYZ was entered ODC PQRS-3' or '223344 HARRY was gone out of ODC AMD-3'

Please suggest efficient way to retrieve such text.

Solution

  • I would be inclined to use stuff():

    select stuff(col, 1, chardindex('was ', col + 'was ') + 4, '')

    The + 'was + in the charindex() function just guarantees no error if 'was ' is not in the text.