String update in SQL Server

Currently I have varchar field. The delimiter is "$P$P$".

The delimiter will appear at least once and at most twice in the varchar data.

Eg.

Sample Heading$P$P$Sample description$P$P$Sample conclusion

Sample Heading$P$P$Sample Description

If the delimiter appears twice, I need to insert a text before the second occurance of the delimiter.

Eg:

Sample Heading$P$P$Sample DescriptionINSERT TEXT HERE$P$P$Sam开发者_JS百科ple Conclusion

If the delimiter occurs only once, then I need to insert a text at the end of the field.

Eg:

Sample Heading$P$P$Sample DescriptionAPPEND TEXT HERE

How this can be done in SQL query?

If you are going to do a lot of string manipulation you might want to use a CLR (.net) function. Since SQL Server isn't exactly made for string manipulation.

Or even better, pull this data back to your application and do it in code.

I even think you can't do it using the default SQL Server String functions

The CharIndex function has an optional 3rd parameter that allows you to specify the starting position of the search. You can use this to find the 2nd occurrence of a string using CharIndex. You can also use the stuff function to insert a string in to another string.

Example:

Declare @Temp Table(Data VarChar(8000))

Insert Into @Temp Values('Sample Heading$P$P$Sample description$P$P$Sample conclusion')
Insert Into @Temp Values('Sample Heading$P$P$Sample Description')

Select len(Data),
        CharIndex('$P$P$', Data + '$P$P$',CharIndex('$P$P$',Data) + 1),
        Stuff(Data + ' ', CharIndex('$P$P$', Data + '$P$P$',CharIndex('$P$P$',Data) + 1), 0, 'Text Here')
From   @Temp

I realize it looks like a mess, but I do encourage you to understand how this works because you may need something similar in the future.

instead of using delimiters, why not creating 3 columns or if you only want one --> an xml field?