Craig Harley Craig Harley - 1 year ago 76
SQL Question

Removing unwanted characters from string using javascript regex for feeding into MYSQL statement

Hi I need to remove some unwanted characters from a string, I'm trying to make a regex to match the required characters but I feel like I'm miles off and don't want to create false positives.
Some help would be very much appreciated.

The starting string looks like:

'SELECT \t* \nFROM \tAudit_Log a WHERE changedate > \'2016-07-21T18:51:41.900Z\''

however this doesn't get parsed correctly by MYSQL, so it needs to look like:

'SELECT * FROM \tAudit_Log a WHERE changedate > '2016-07-21T18:51:41.900Z''

I've tried this but not having much luck:

str = str.replace('\t', '');
str = str.replace('\n', '');
str = str.replace('\\', '');

sln sln
Answer Source

If escape plus a-z is the culprit, simplify it to two cases:

  1. Escape + not a-z nor escape
  2. Escape + a-z or escape

In the first case, the not a-z (punctuation) is written back.
In the second case, its not.

Find: (?i)\\(?:([^\\a-z])|[\\a-z])?
Replace: $1 or \1


 (?i)                   # Case insensitive
 \\                     # '\' To be removed
 (?:                    # Cluster start
      ( [^\\a-z] )           # (1), Punctuation written back
   |                       # or,
      [\\a-z]                # The rest, to be removed
 )?                     # Cluster end, and is optional for EOS
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download