update a string in a colunm - SQL Server Forums

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

update a string in a colunm

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-04-25 : 08:15:21

Afternoon all.
I have a table which has a column with has been inserting incorrect data, here is an example. Column name is description and table name is tblClientUsage:
[companyname]: 288bsl | | 13/03/2007
[companyname]: 288asl | | 08/03/2007
[companyname]: RES04sl | | 19/02/2004
[companyname]: RES01sl | | 19/02/2004
[companyname]: AAsl | | 31/12/2005
[companyname]: 652Asl | | 05/02/2007
[companyname]: GAZ1sl | | 20/03/2007
[companyname]: AAsl | | 31/12/1999
[companyname]: 363asl | | 20/01/2007
[companyname]: 288b1sl | | 02/11/2006
[companyname]: 288bsl | | 02/11/2006
Now what has been happening is that is has not been putting the company name in the column, just the text companyname. Now i have a table which has the company name and company id, i can easily join the two tables together but how can i replace just the companyname text in the description column with leaving the text after.

Any help would be great

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 08:26:24

Something like this?

Update t
Set Description = Replace(t.description, '[companyname]', '[' + c.companyname + ']')
from SomeTable t join Company c
on <some-condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 08:27:59

[code]
update u
set description = replace([description], '[companyname]', c.company_name
from tblClientUsage u inner join company c
on u.[description] like '%' + c.company_id + '%'
where [description] like '[companyname]%'
[/code]

lemondash
Posting Yak Master

159 Posts

Posted - 2007-04-25 : 08:34:56

Looks to have done the trick !!!!!!!

Cheers !!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources