Simple replace question 2

[Naoise]

declare @foo varchar(50)
set @foo = '1.1.1.1'

select replace(@foo, '1.1','1.2')

This gives me 1.2.1.2 as it replaces all occurrences of 1.1 in @foo, how would I only replace the beginning of @foo with 1.2?

I will always know the length of the string to replace if that helps?

 

[SQLDenis]

declare @foo varchar(50)
set @foo = '1.1.1.1'

select replace(@foo, '1.1.','1.2.')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

In that particular case:

declare @foo varchar(50)
set @foo = '1.1.1.1'

select replace(@foo, '1.1.','1.2.')

But if you want to replce just the first occurance of the string no matter where it resides, you could try:

[COLOR=blue]declare[/color] @foo [COLOR=blue]varchar[/color](50)
[COLOR=blue]declare[/color] @stringtobereplaced [COLOR=blue]varchar[/color](50)
[COLOR=blue]declare[/color] @stringforreplace   [COLOR=blue]varchar[/color](50)

[COLOR=blue]declare[/color] @first [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @foo = [COLOR=red]'1.1.1.1'[/color]
[COLOR=blue]set[/color] @stringtobereplaced = [COLOR=red]'1.1'[/color]
[COLOR=blue]set[/color] @stringforreplace   = [COLOR=red]'1.2'[/color]
[COLOR=blue]SET[/color] @first = [COLOR=#FF00FF]CHARINDEX[/color](@stringtobereplaced,@foo)
[COLOR=blue]IF[/color] @first <> 0
   [COLOR=blue]BEGIN[/color]
       [COLOR=blue]SET[/color] @foo = [COLOR=#FF00FF]LEFT[/color](@foo, @first-1)+
                  @stringforreplace   +
                  [COLOR=#FF00FF]SUBSTRING[/color](@foo,@first+LEN(@stringtobereplaced),8000)
   [COLOR=blue]END[/color]
[COLOR=blue]SELECT[/color] @foo

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[monksnake]

If you always know the length and the string you want to replace is always at the beginning of the string, you can do this:

declare @foo varchar(50)
set @foo = '1.1.1.1'

select replace(left(@foo, 3), '1.1','1.2') + right(@foo, len(@foo)- 3)

Where 3 is the length, which you can put into a varable if you want.


<.

 

[Naoise]

I don't think that is going to work

declare @foo varchar(50)
set @foo = '1.1.1.1.1'

select replace(@foo, '1.1','1.2')

In effect I wish to replace the first Len('1.1') characters with '1.2'

The '1.1' argument could be anything, '1.3.5.1.2.7' etc

 

[tran008]

declare @foo varchar(50)
set @foo = '1.1.1.1'

select replace((left(@foo,3)), '1.1','1.2')+right(@foo,len(@foo)-3)

 

[SQLDenis]

in that case do this

declare @foo varchar(50)
set @foo = '1.1.1.1.1'

select '1.2.' + right(@foo,len(@foo) -4)
go


declare @foo varchar(50)
set @foo = '1.3.5.1.2.7'

select '1.2.' + right(@foo,len(@foo) -4)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

This will replace the first occurrence:

[COLOR=blue]declare[/color] @foo [COLOR=blue]varchar[/color](50)
[COLOR=blue]set[/color] @foo = [COLOR=red]'1.1.1.1'[/color]

[COLOR=green]--here goes
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]stuff[/color](@foo, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'1.1'[/color], @foo), len([COLOR=red]'1.1'[/color]), [COLOR=red]'1.2'[/color])

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

I see a lot of options here. I guess the question is, what do you want to see when you get this for @foo:

1.7.9.1.1.3.2.1.1 ?

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

The main question is, do you always want to replace the beginning of the string?? If not, then none of these solutions will do.

<.

 

[gmmastros]

Hey Alex, that's some pretty cool [!]stuff[/!] code.

-George

"the screen with the little boxes in the window." - Moron

 

[Naoise]

Thanks one and all

 

[AlexCuse]

I don't get to use stuff very often. Fun times

Naoise - glad you've got it working!



Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

The only reason Alex beat me to that code was because I had to leave for work.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]