Shardul Shardul - 10 months ago 43
SQL Question

How to select specific text from the string in efficient way for approx 5 Lakh records using SQL

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

SET @DESC_MESSAGE = '12345 VENKAT was entered ODC ABCD-3'

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 Source

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.