Updating SUBSTRING column - 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 2008 Forums

Transact-SQL (2008)

Updating SUBSTRING column

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2011-11-15 : 07:25:20

Hi,

Is there any way I can UPDATE few bits from a wide string colulmn?

My tblA has one column in it: TDM_Data nvarchar(600)


UPDATE pre
		SET	
			substring(pre.TDM_Data,60,6) = CAST( SPACE(6 - LEN(a.tblB)) + CAST(a.tblB AS VARCHAR))
FROM	tblA AS pre 
			INNER JOIN tblB AS a
				ON	LTRIM(RTRIM(substring(pre.TDM_Data,3,14))) =	LTRIM(RTRIM(a.ID))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 07:29:32

you need to use STUFF for this


UPDATE pre
		SET	pre.TDM_Data =STUFF(pre.TDM_Data,60,6,CAST( SPACE(6 - LEN(a.tblB)) + CAST(a.tblB AS VARCHAR)))
FROM	tblA AS pre 
			INNER JOIN tblB AS a
				ON	LTRIM(RTRIM(substring(pre.TDM_Data,3,14))) =	LTRIM(RTRIM(a.ID))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 07:31:03

Also see what happens if you omit the length value while casting to varchar

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

umertahir
Posting Yak Master

154 Posts

Posted - 2011-11-15 : 07:40:49

quote:
Originally posted by visakh16

you need to use STUFF for this

UPDATE pre
SET pre.TDM_Data =STUFF(pre.TDM_Data,60,6,CAST( SPACE(6 - LEN(a.tblB)) + CAST(a.tblB AS VARCHAR)))
FROM tblA AS pre
INNER JOIN tblB AS a
ON LTRIM(RTRIM(substring(pre.TDM_Data,3,14))) = LTRIM(RTRIM(a.ID))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi,

Thanks for your reply.

I tried your code on my actual code


UPDATE	pre
		SET TDM_Data = STUFF(pre.TDM_Data,60,6, CAST(SPACE(6 - LEN(a.Previous_NumberOfHerds)) + CAST(a.Previous_NumberOfHerds AS VARCHAR)))
FROM	ImportTDMDataForHOL_113_ManualUpdateSchemeCodeRejections AS pre 
			INNER JOIN tblTDM_HOL_BullWithLostDaughters_113_14Nov2011 AS a
				ON	LTRIM(RTRIM(substring(pre.TDM_Data,3,14)))			=	LTRIM(RTRIM(a.HBN))		AND
					substring(pre.TDM_Data,1,2)							=	a.BreedId	AND
					substring(pre.TDM_Data,17,1)						=	a.IDType		AND
					Substring(pre.TDM_Data,18,1)						=	a.PedigreeStatus
WHERE	a.UpdateStatus IS NOT NULL

Error:
Msg 1035, Level 15, State 10, Line 2
Incorrect syntax near 'CAST', expected 'AS'.

umertahir
Posting Yak Master

154 Posts

Posted - 2011-11-15 : 07:47:50

correct syntax:


UPDATE	pre
		SET TDM_Data = STUFF(pre.TDM_Data,60,6, CAST( SPACE(6 - LEN(a.Previous_NumberOfHerds)) + CAST(a.Previous_NumberOfHerds AS VARCHAR) AS VARCHAR))
FROM	ImportTDMDataForHOL_113_ManualUpdateSchemeCodeRejections AS pre 
			INNER JOIN tblTDM_HOL_BullWithLostDaughters_113_14Nov2011 AS a
				ON	LTRIM(RTRIM(substring(pre.TDM_Data,3,14)))			=	LTRIM(RTRIM(a.HBN))		AND
					substring(pre.TDM_Data,1,2)							=	a.BreedId	AND
					substring(pre.TDM_Data,17,1)						=	a.IDType		AND
					Substring(pre.TDM_Data,18,1)						=	a.PedigreeStatus
WHERE	a.UpdateStatus IS NOT NULL

Thanks a lot for your help, really appreciate. cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 09:55:56

quote:

Originally posted by umertahir

correct syntax:


UPDATE	pre
		SET TDM_Data = STUFF(pre.TDM_Data,60,6, CAST( SPACE(6 - LEN(a.Previous_NumberOfHerds)) + CAST(a.Previous_NumberOfHerds AS VARCHAR) AS VARCHAR))
FROM	ImportTDMDataForHOL_113_ManualUpdateSchemeCodeRejections AS pre 
			INNER JOIN tblTDM_HOL_BullWithLostDaughters_113_14Nov2011 AS a
				ON	LTRIM(RTRIM(substring(pre.TDM_Data,3,14)))			=	LTRIM(RTRIM(a.HBN))		AND
					substring(pre.TDM_Data,1,2)							=	a.BreedId	AND
					substring(pre.TDM_Data,17,1)						=	a.IDType		AND
					Substring(pre.TDM_Data,18,1)						=	a.PedigreeStatus
WHERE	a.UpdateStatus IS NOT NULL

Thanks a lot for your help, really appreciate. cheers

i just copied your suggestion as it is
didnt notice you missed an AS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources