0

I have a table which I update using a stored procedure. One column is for the image Url. The code in the stored procedure looks like:

UPDATE Products 
SET ImageUrl = 'https://images.XXXXXXX.com/lm/image/s/'+RIGHT(Source,2)+'/'+Source+'_'+Code+'.203'

I need the url to be in a single line in the cell however, it splits the url right before .203 when writing it into the cell. So, in the cell, it is like;

 https://images.XXXX.com/lm/image/s/ab/g671235_12312
.203

It normally is no problem for me but I use this data in XML. And Since the Url is not in one line, the remote server I connect does not update the image when I submit the XML. When I manually fix the Url and put it in one line, it just works fine. I googled and searched to find a way to fix this issue, but I could not find a solution. Any help will be appreciated.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rick
  • 21
  • 4
  • 1
    That looks a UI formatting issue, nothing to do with SQL. Make the cell in your UI about four characters wider... *(Unless the `code` field has a carriage-return in it? In which case, just remove the carriage returns from the `code` field values?)* – MatBailie Apr 06 '20 at 15:49
  • By 'cell' you mean some kind of results grid? And it's introducing a line break when it wraps? – shawnt00 Apr 06 '20 at 15:50
  • @shawnt00 Correct. – Rick Apr 06 '20 at 15:57

1 Answers1

0
  • It sounds like there is a carriage return/newline feed captured within your "Code" column. I have encountered this many times when users manually fiddle with values (they update the value and then hit thinking this will apply the value when this is just inserting the r\n\ values within the column).

To double check if this is the case:

  1. Locate the value that is causing this line break to appear
  2. Copy that cell value (I assume you are using SSMS)
  3. Open Notepad++, Sublime, or similar editor that will display non-printable characters
  4. Set your editor to display non-printable characters (in Notepad++ it is View > Show Symbols > Show All Characters)
  5. This should then display the line break characters that are causing your headaches

Quick solution: Scrub \r\n values - https://stackoverflow.com/a/951705/8026186

More Ideal Solution Prevention of \n\r insertion If you have access to the data being entered in the database, the best way to avoid this is to prevent the \r\n from making it into the cell in the first place. The quick solution will work in case you don't have the leverage to control initial input. However, from past experience, you will want to avoid non-printable values from appearing in the first place.

Hope this helps!

bip
  • 18
  • 3