Update question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Update question

Author

Topic

frank2
Starting Member

35 Posts

Posted - 2005-09-12 : 15:10:49

I have a table in which one column contains a string. I want to replace the first x characters of that column with another string where the first x characters match another string.

I think I should use the LEFT function to test for the left most characters I want and REPLACE function.

What I need to know is how do I do that in a SELECT statement?

for example
MyCol
-----
abctest
testabc
abchere

I need to replace abc only where it is the first 3 characters of the string.

thanks for the help

homebrew
Posting Yak Master

114 Posts

Posted - 2005-09-12 : 15:20:08

What do you want to replace "abc" with ?

Update TableA
set substring(Mycol,1,3) = 'NewValue"
where substring(Mycol,1,3) = 'abc'

frank2
Starting Member

35 Posts

Posted - 2005-09-12 : 16:21:22

I keep getting a this error
Line 2: Incorrect syntax near '('.

homebrew
Posting Yak Master

114 Posts

Posted - 2005-09-12 : 17:01:49

If you post your actual code, then it will be easier to troubleshoot. Sounds like you've got an extra '(' in there somewhere.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-12 : 17:11:29

quote:
Originally posted by homebrew

What do you want to replace "abc" with ?

Update TableA
set substring(Mycol,1,3) = 'NewValue"
where substring(Mycol,1,3) = 'abc'

I don't think you can set a substring to a value...

try this example:


Declare @myTable table (a varchar(100))
Insert Into @myTable
Select 'abc1234' Union All
Select 'testabc' Union All
Select ' abc3456' Union All
Select 'abc5678'

Select * From @myTable

Update A
Set a = stuff(a,1,3,'anything')
From @myTable A
Where left(a,3)='abc'

Select * From @myTable

Corey