replace() question

Question

Post reply

replace() question

bluepaperbag

SSCrazy

Points: 2055
More actions
October 18, 2006 at 7:56 am

#153238

declare @table table (ad_str1 varchar(50))
insert @table
select 'apple avenue w' union all
select 'melon road e' union all
select 'watermelon circle n'union all
select 'banana durian crossing s'
select * from @table
declare @temp varchar(20)
select @temp = substring((ad_str1),charindex(' ',(ad_str1),1)+1,len((ad_str1))) from @table
print(@temp)
update @table
set @temp = replace(@temp, substring(@temp,1,charindex(' ',@temp,1)-1), 'internet')
where ltrim(reverse(substring(reverse(ad_str1), 1, charindex(' ', reverse(ad_str1))))) in ('w','e','s','n')
select @temp = substring(@temp,1,charindex(' ',@temp,1)-1)
print (@temp)
select * from @table
-------------------------------------------------------------------------------------------------------------------------
Im trying to replace the string next to S,W,N,E to 'internet'. But seems like it's not working. Any input will be appreaciated.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

Try this

declare @table table (ad_str1 varchar(50))

insert @table

select 'apple avenue w' union all

select 'melon road e' union all

select 'watermelon circle n'union all

select 'banana durian crossing s'

select * from @table

UPDATE @Table

SET ad_Str1 = CASE WHEN CHARINDEX(' W',ad_str1) > 0 THEN REPLACE(ad_str1,' W',' internet')

ELSE CASE WHEN CHARINDEX(' e',ad_str1) > 0 THEN REPLACE(ad_str1,' e',' internet')

ELSE CASE WHEN CHARINDEX(' n',ad_str1) > 0 THEN REPLACE(ad_str1,' n',' internet')

ELSE CASE WHEN CHARINDEX(' s',ad_str1) > 0 THEN REPLACE(ad_str1,' s',' internet') ELSE ad_str1 END END END END

FROM @table

SELECT * FROM @Table

Ram

bluepaperbag SSCrazy Points: 2055 More actions · Answer 2

This is what i want.

SAMPLE

apple avenue w

melon road e

watermelon circle n

banana durian crossing s

apple1 avenue w

melon1 road e

watermelon1 circle n

banana1 durian crossing s

RESULT

apple INTERNET w

melon INTERNET e

watermelon INTERNET n

banana durian INTERNET s

apple1 INTERNET w

melon1 INTERNET e

watermelon1 INTERNET n

banana1 durian INTERNET s

Ram Ram Ten Centuries Points: 1079 More actions · Answer 3

Ok, Got it

declare @table table (ad_str1 varchar(50))

insert @table

select 'apple avenue w' union all

select 'melon road e' union all

select 'watermelon circle n'union all

select 'banana durian crossing s'

UPDATE @table

SET ad_str1=

REPLACE(ad_str1,

(

CASE WHEN CHARINDEX(' W',ad_str1) > 0 THEN REPLACE(ad_str1,' W','')

ELSE CASE WHEN CHARINDEX(' e',ad_str1) > 0 THEN REPLACE(ad_str1,' e','')

ELSE CASE WHEN CHARINDEX(' n',ad_str1) > 0 THEN REPLACE(ad_str1,' n','')

ELSE CASE WHEN CHARINDEX(' s',ad_str1) > 0 THEN REPLACE(ad_str1,' s','') ELSE ad_str1 END END END END

&nbsp

,

(

REVERSE(

SUBSTRING

(

REVERSE(

(CASE WHEN CHARINDEX(' W',ad_str1) > 0 THEN REPLACE(ad_str1,' W','')

ELSE CASE WHEN CHARINDEX(' e',ad_str1) > 0 THEN REPLACE(ad_str1,' e','')

ELSE CASE WHEN CHARINDEX(' n',ad_str1) > 0 THEN REPLACE(ad_str1,' n','')

ELSE CASE WHEN CHARINDEX(' s',ad_str1) > 0 THEN REPLACE(ad_str1,' s','') ELSE ad_str1 END END END END)),

CHARINDEX(SPACE(1),

REVERSE(

(CASE WHEN CHARINDEX(' W',ad_str1) > 0 THEN REPLACE(ad_str1,' W','')

ELSE CASE WHEN CHARINDEX(' e',ad_str1) > 0 THEN REPLACE(ad_str1,' e','')

ELSE CASE WHEN CHARINDEX(' n',ad_str1) > 0 THEN REPLACE(ad_str1,' n','')

ELSE CASE WHEN CHARINDEX(' s',ad_str1) > 0 THEN REPLACE(ad_str1,' s','') ELSE ad_str1 END END END END)))+1

,255

&nbsp) + ' internet'

&nbsp

SELECT * FROM @table

Ram

bluepaperbag SSCrazy Points: 2055 More actions · Answer 4

Sriram Ramamoorthy,

your query doesn't work

Server: Msg 170, Level 15, State 1, Line 15

Line 15: Incorrect syntax near ','.

Ram Ram Ten Centuries Points: 1079 More actions · Answer 5

Hello,

It is a syntax error.

Replace "Wink" face with ")", then it will work

bluepaperbag SSCrazy Points: 2055 More actions · Answer 6

how can i just select the word next to w,e,s,n?

avenue,road, circle, crossing

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

There's probably a dozen ways... here's a couple without UDF or Tally table...

declare @table table (ad_str1 varchar(50))

insert @table

select 'apple avenue w' union all

select 'melon road e' union all

select 'watermelon circle n'union all

select 'banana durian crossing s'

If your strings never have more than 3 spaces and no periods, this works nasty fast...

SELECT PARSENAME(REPLACE(ad_str1,' ','.'),2)

FROM @table

If it has more than 3 spaces or contains periods, then this will work so long as the last two characters are a space followed by N, S, E, or W...

SELECT REVERSE(SUBSTRING(SUBSTRING(REVERSE(ad_strl),3,50),1,CHARINDEX(' ',SUBSTRING(REVERSE(ad_strl),3,50))))

FROM @table

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bluepaperbag SSCrazy Points: 2055 More actions · Answer 8

bluepaperbag

SSCrazy

Points: 2055

October 19, 2006 at 7:32 am

#666783

jeff, thanks

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

You're welcome... thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)