String update in SQL Server

Question

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$Sample 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?

Answer 1

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

Answer 2

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.

Answer 3

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