Tsql find javascript \\t in sql column

• Siten0308

  SSCarpal Tunnel

  Points: 4404

  More actions

  July 6, 2021 at 4:26 am

  Go to Answer

  #3903741

  hello

  Not sure if anyone has run into this issue but we have a column in sql that has an open varchar(max) that sometimes people put in JavaScript meta data like

  This is a test\t

  Or

  How are you doing today\n

  Or

  The weather\n is nice

  And when I try to search for the JavaScript meta data like

  Select * from table1 where freeText like '%\n%' or freeText like '%\t%'

  But it never finds the columns or if it does the columns would display as though the JavaScript metadata is not there like

  The weather is nice today

  Or

  How are you doing today

  Has anyone experience this in sql server 2014 sp2? Is there a work around to finding them?

   

  Thanks on advance

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  July 6, 2021 at 9:25 pm

  Answer

  #3904151

  SSMS, in the "results to Grid" mode will strip off any special characters when presenting it to the UI.  What I mean is if you have a NEW LINE in your string, it will present it in once cell.  This can be confirmed with something like this:

  SELECT 'HELLO
  
  
  WORLD'

  You can clearly see there are newline characters in the string, but they are NOT presented in the results to Grid mode.

  Now, if you switch over to Results to Text mode, you will see it presented with the new lines in it.

  Next, as for \n and \t in the column, are they the literal characters \n (ie a slash character and the n character) OR is it a newline (ie CHAR(13) and CHAR(10) aka CRLF)?  If it is the latter, then searching for "\n" isn't going to find it as \n is NOT the same thing as CRLF.  My GUESS without seeing your data is that it isn't the literal characters being put into the table, but is their representation being put in.  So if you change your WHERE to something like:

  WHERE freeText LIKE
  -- New Line
  '%' + CHAR(13) + CHAR(10) + '%'
  OR freeText LIKE
  -- Tab
  '%%'

   

  One way you can verify that this works is by using my first example and REPLACE with something like this:

  SELECT
  REPLACE(   REPLACE(  REPLACE( 'HELLO
  
  
  WORLD'
  , CHAR(13)
  , 'x'
   )
    , CHAR(10)
    , 'y'
    )
     , ''
     , 'z'
     );

  That last query is replacing CHAR(13) with x, char(10) with y, so you can see that a newline is coming out as xy.  Then it is replacing a tab with z.  So as a result you now get "HELLOzxyxyzxyWORLD".  so, HELLO\t\n\n\t\nWORLD using the \n \t syntax.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.