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?

Might I sugest three separate fields?

Nate
– Nate

2010-06-09 18:47:49 +00:00
Commented Jun 9, 2010 at 18:47 — Nate
– Nate, Commented Jun 9, 2010 at 18:47

LeonG · Accepted Answer · 2010-06-09 18:21:50Z

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

answered Jun 9, 2010 at 18:21

LeonG

8814 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

George Mastros · Accepted Answer · 2010-06-09 19:07:28Z

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.

Tim Mahy · Accepted Answer · 2010-06-09 19:10:10Z

0

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

answered Jun 9, 2010 at 19:10

Tim Mahy

1,31913 silver badges28 bronze badges

Collectives™ on Stack Overflow

String update in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related