Using SUBTRING For a Destination Field

• EddieN1

  SSC-Addicted

  Points: 492

  More actions

  August 23, 2014 at 11:20 am

  #289370

  I have a Function where I want to put a character in a particular position in a Destination string. For example, I have a 5 character field containing, say, "12345". I would like to put the letter "A" in the third position resulting in "12A45". I'm trying the following

  DECLARE @RetVal as varchar(5)

  SET SUBSTRING(@RetVal,3,1) = 'A'

  I'm getting a compiler error

  "incorrect syntax near '@RetVal'. Expecting '(' or SELECT"

  Am I using the wrong Function to do this? If so, what should I be using?

  Thanks, Eddie

• Phil Parkin

  SSC Guru

  Points: 247037

  More actions

  August 23, 2014 at 11:38 am

  #1740488

  You can STUFF it!

  DECLARE @Val1 varchar(5) = '12345'

  declare @RetVal varchar(10)

  set @RetVal = STUFF(@Val1,3,1,'A')

  select @Val1

  , @RetVal

  ---

• EddieN1

  SSC-Addicted

  Points: 492

  More actions

  August 23, 2014 at 3:06 pm

  #1740507

  That worked... thanks, Eddie