how to update a feid with part of its content

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

General SQL Server Forums

New to SQL Server Programming

how to update a feid with part of its content

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2009-04-26 : 05:32:47

I try to write a command that will get the content of cells that contain a string that has a specific substing and to update this cell with a new string - the new string has to be the original string but the substring has to be replaced with some other string.

for example:
change evry cell in the column Name that has substring of "je"
to "ja"
jeck will change to jack

is it possible??

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-26 : 05:44:54

Try like this:
update table set column=replace(column,'searchvalue','replacevalue')

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

elic05
Yak Posting Veteran

62 Posts

Posted - 2009-04-26 : 05:50:10

But the searchValue has to be taken from that same specific cell.
I think that instead searchValue i will have to use a select command.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-26 : 05:53:13

An example would be great.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

elic05
Yak Posting Veteran

62 Posts

Posted - 2009-04-26 : 06:08:16

OK, got it you are 100% write.
but its a bit more comlicated because i use a column to hold a few categories names like that
organic*shugarFree*glutanFree
and if a category's name has to be changed i use this command
it must be replaced only if the searchvalue fits between 2 * (or if its the first one it has only one *)
can I still use your way?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-26 : 06:20:15

Maybe you can add a WHERE-clause.
where substring(column,1,11)='shugarFree*'
or column like '%*shugarFree*%'

not tested

Webfred
Edit: Oh no, it is not right - wait...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-26 : 06:38:26

I have only the idea to make it in two steps like following example:

declare @Sample table(id int identity(1,1), col1 varchar(255))
insert @Sample
select 'organic*shugarFree*glutanFree' union all -- should be updated
select 'shugarFree*organic*glutanFree' union all -- should be updated
select 'organic*glutanFree*shugarFree'           -- should not be updated

select * from @Sample

update @Sample set col1=replace(col1,'*shugarFree*','*newValue*')
update @Sample set col1='newValue*' + substring(col1,12,len(col1)-11) where substring(col1,1,11)='shugarFree*'

select * from @Sample

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

elic05
Yak Posting Veteran

62 Posts

Posted - 2009-04-26 : 06:43:19

is that what you mean?
update table set column=replace(column,@find,@change)
where substring(column,0,@find.length+1) = @find+"*" // if it is in the beginig of the string
or substring(column,column.length-@find.length,@find.length+1) = "*"+@find //if it is at the end of the string
or column like "*"+@find +"*" //if it is in the middle

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-26 : 06:50:55

Maybe the problem is that @find has more than one occurence in the column.
So I decided for two updatesteps.
Another chance could be a CASE-statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

elic05
Yak Posting Veteran

62 Posts

Posted - 2009-04-26 : 11:02:13

I am ready to do it even in 5 steps...
but its not working (I get 0 rows effected)
I have in the category column milki*arye
and i try to replace arye to moshe
i wrote:
UPDATE myProducts
SET category = SUBSTRING(category, 0, LEN(category) - LEN(@find)) + @change
WHERE (SUBSTRING(category, LEN(category) - LEN(@find) - 1, LEN(@find) + 1) = '*' + @find)

I tried also:

UPDATE myProducts
SET category = replace(category,@find, @change)
WHERE (SUBSTRING(category, LEN(category) - LEN(@find) - 1, LEN(@find) + 1) = '*' + @find)

why its not working??

elic05
Yak Posting Veteran

62 Posts

Posted - 2009-04-26 : 11:33:47

ok, got it
substring start to count from 1!!

thanks a lot webfred i couldnt make it without you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources