Shardul Shardul - 7 days ago 5
SQL Question

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.

Answer

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.

Comments