Replace function not working

• spin

  Hall of Fame

  Points: 3274

  More actions

  March 9, 2023 at 2:57 pm

  #4158861

  Hi

  I have some data which is logging web errors. It returns data that looks similar to this...

  Array ( [error] => Quote not found [module] => api [detail] => 10038 )

  I need to replace the Array ( [error] => with an empty string. So the end result looks like...

  Quote not found [module] => api [detail] => 10038 )

  However, when I use the REPLACE function it doesn't actually do anything. If I only replace the word Array then it works. As soon as I extend the replace string to include the rest of the text i want to replace it doesn't work.

  The data type is NVARCHAR(MAX)

  Can anyone help?

  Thanks

   

   

   

   

   

  • This topic was modified 1 year, 8 months ago by  spin.

• spin

  Hall of Fame

  Points: 3274

  More actions

  March 9, 2023 at 3:11 pm

  #4158875

  Hi

  I have solved this now, turn out the data had carriage retuens, tabs and line feeds in so have had to use something like..

  SELECT REPLACE(REPLACE(REPLACE(REPLACE([error], CHAR(13), ''), CHAR(10), ''), char(9), '') ,'Array( [error] =>','')
  

   

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  March 9, 2023 at 6:34 pm

  #4158969

  Maybe STUFF() would work better if this text is always at the beginning.

  SELECT STUFF([error], 1, CHARINDEX('Quote', [error])-1, '')

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Steve Collins

  SSCrazy

  Points: 2157

  More actions

  March 9, 2023 at 7:44 pm

  #4159000

  Could be a type mismatch due to the search string not being specified with the Unicode prefix N'...'

  declare @json nvarchar(max)=N'Array ( [error] => Quote not found [module] => api [detail] => 10038 )';
  
  select replace(@json, N'Array ( [error] => ', N'');

   

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können