How to use double replace with 2 items in 1 statement

• Weegee2017

  SSC Eights!

  Points: 994

  More actions

  July 6, 2018 at 9:59 am

  #414322

  Hi there

  I have the following replace statement

  declare

  @DesignationStartPosition TINYINT,

  @DesignationMaxLength TINYINT

  SET @DesignationMaxLength = 255

  SET @DesignationStartPosition = 1

  declare @AccountName varchar(1000) = 'ACCOUNT A/C'

  SELECT

  SUBSTRING(LTRIM(RTRIM((REPLACE(@AccountName,'A/C','')))),@DesignationStartPosition,@DesignationMaxLength)

  which gives me ACCOUNT

  How can i do a double replace to replace the string 'A/C' at the same time as 'ACCOUNT' is replaced?

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  July 6, 2018 at 10:04 am

  #1996581

  Weegee2017 - Friday, July 6, 2018 9:59 AM

  Hi there

  I have the following replace statement

  declare

  @DesignationStartPosition TINYINT,

  @DesignationMaxLength TINYINT

  SET @DesignationMaxLength = 255

  SET @DesignationStartPosition = 1

  declare @AccountName varchar(1000) = 'ACCOUNT A/C'

  SELECT

  SUBSTRING(LTRIM(RTRIM((REPLACE(@AccountName,'A/C','')))),@DesignationStartPosition,@DesignationMaxLength)

  which gives me ACCOUNT

  How can i do a double replace to replace the string 'A/C' at the same time as 'ACCOUNT' is replaced?

  Right now you are replacing 'A/C'. If you want to replace both, you could just add another REPLACE:

  SELECT SUBSTRING(LTRIM(RTRIM((REPLACE(REPLACE(@AccountName,'A/C',''), 'ACCOUNT', '')))),@DesignationStartPosition,@DesignationMaxLength)


  but I'm not sure what your end goal is....

  Mike Scalise, PMP
  https://www.michaelscalise.com