Replace Nth character of a character

[bmacbmac]

Hi,

In Mytable I have a column Mycolumn. It has data like this:

DBK^3^421.001
DBK^3^422.001
DBK^19^1.001

I would like to replace the 2nd instance of ^ with _

So,

DBK^3_421.001
DBK^3_422.001
DBK^19_1.001

I tried some CHARINDEX methods, but just can't get it to work. Any ideas?

Thanks!

 

[gmmastros]

The CharIndex function has an optional 3rd argument for "Start Position". This means you can find the position of the second occurance like this:

Select CharIndex('^', YourColumnName, CharIndex('^', YourColumnName) + 1)
From   YourTable

You can use the STUFF function to replace strings.

Putting it all together:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('DBK^3^421.001')
Insert Into @Temp Values('DBK^3^422.001')
Insert Into @Temp Values('DBK^19^1.001')

Select CharIndex('^', Data, CharIndex('^', Data) + 1)
From   @Temp

Select Stuff(Data, CharIndex('^', Data, CharIndex('^', Data) + 1), 1, '_')
From   @Temp

Be a little careful with this code. I haven't tested what would happen if there are not 2 (or more) occurances of ^.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

That worked great thanks! I never had more than 2 instances so no worries. Thanks again!