SET a value with SUBSTRING - 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)

SET a value with SUBSTRING

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-03-09 : 09:58:50

Hi, I am trying to run the following update query but get an error , Incorrect Syntax near '('
any ideas ?

UPDATE dbo.ClientDetail
SET SUBSTRING(MemoIndicators, 23, 1) = 'M'
WHERE SUBSTRING(MemoIndicators, 23, 1) = 'Z' AND SUBSTRING(MemoIndicators, 14, 1) = 'Y'

regards,
J

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-09 : 10:09:29

try this:


UPDATE dbo.ClientDetail
SET MemoIndicators = left(MemoIndicators, 23) + 'M' + substring(MemoIndicators, 24, len(MemoIndicators))
WHERE SUBSTRING(MemoIndicators, 23, 1) = 'Z' AND SUBSTRING(MemoIndicators, 14, 1) = 'Y'

you'll probably need to change the char positions...

Go with the flow & have fun! Else fight the flow

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-03-10 : 04:14:32

I receive this error :
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 05:03:03

as i said you have to play with the length of the left and substring functions.
basicaly you want: 'CharsBeforeM' + 'M' + 'CharsAfterM'

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-10 : 06:50:04

quote:
Originally posted by spirit1

try this:

UPDATE dbo.ClientDetail
SET MemoIndicators = left(MemoIndicators, 23) + 'M' + substring(MemoIndicators, 24, len(MemoIndicators))
WHERE SUBSTRING(MemoIndicators, 23, 1) = 'Z' AND SUBSTRING(MemoIndicators, 14, 1) = 'Y'

you'll probably need to change the char positions...

Go with the flow & have fun! Else fight the flow

Try this

UPDATE dbo.ClientDetail
SET MemoIndicators = left(MemoIndicators, 23) + 'M' + substring(MemoIndicators, 25, len(MemoIndicators))
WHERE SUBSTRING(MemoIndicators, 23, 1) = 'Z' AND SUBSTRING(MemoIndicators, 14, 1) = 'Y'

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-03-10 : 07:13:46

cheers,
but I actually needed to do
SET MemoIndicators = left(MemoIndicators, 22) + 'M' + substring(MemoIndicators, 24, len(MemoIndicators))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources