How to use wildcards in replace statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to use wildcards in replace statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-19 : 11:20:23

Janie writes "I have field values where the first word is the same in every row and the second word changes with each row.
I need to remove the first word keep the second word and add a third word.
For Example, row reads "sub books"
i need to change it to "books substitute"
word one and three will be the same on each row.

now
sub books
sub door
sub chains

change to
books substitute
door substitute
chains substitue

Help how can I use replace and wildcards to do this??
Also, how can I add one year to a date?
thanks, Janie M"

Jay99

468 Posts

Posted - 2002-03-19 : 15:19:05


declare @firstword varchar(50), @thirdword varchar(50)
select @firstword = 'sub ', @thirdword = ' substitute'

update table
set field = case
    when charindex(@firstword,field)>0 then replace(field, @firstword, '') + @thirdword
    else field end

Jay
<O>

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-19 : 17:36:22

yep - perhaps though the first word is not always "sub" ??
Let us know if not.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-03-20 : 00:06:07

In Case if the first letter isnt always sub.

modify Jay's solution a little .

declare @thirdword varchar(50)
select @thirdword = ' substitute'
update table set field = case when charindex(' ',field)>0 then substring(field,charindex(' ',field)+1,len(field)) + @thirdword else field end

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources