Substring and regex

Question

I have a SQL string:

var sql= "AND DT_FIM IS NULL AND ( CD_JOB, DT_INI_JOB ) IN  (SELECT x.CD_JOB, x.DT_INI FROM PRS_JOBS_MAQUINA x  WHERE x.CD_JOB = ':CD_JOB' AND TO_CHAR(x.DT_INI, 'YYYY-MM-DD') = ':DT_INI_JOB' AND x.DT_FIM IS NULL)

and i need to extract the binded values(:CD_JOB , :DT_INI_JOB), the problem is that with

var bindFields=sql.substring(sql.lastIndexOf("':") + 1 , sql.lastIndexOf("'"));

it returns only the first match and i need both. Is it possible with Javascript? Lodash to the rescue if anybody find it useful.

https://jsfiddle.net/oq0dmyjo/1/ . Apreciate your help . Thanks

Use [`var rex = /':([^']+)/g`](https://regex101.com/r/jN0xR5/1) and loop over the matches with `RegExp#exec` retrieving Group 1 values. (See *code generator* page for the JS code). — Wiktor Stribiżew, Jul 14 '16 at 14:17

score 2 · Answer 1 · edited May 23 '17 at 10:28

2

You can use a very simple regex with a capturing group:

/':([^']+)/g

Explanation:

': - a literal sequence ':
([^']+) - Group 1 capturing 1 or more symbols other than '
g - a global modifier matching all instances.

Here are some resources to learn:

JS code:

var re = /':([^']+)/g; 
var str = "AND DT_FIM IS NULL AND ( CD_JOB, DT_INI_JOB ) IN  (SELECT x.CD_JOB, x.DT_INI FROM PRS_JOBS_MAQUINA x  WHERE x.CD_JOB = ':CD_JOB' AND TO_CHAR(x.DT_INI, 'YYYY-MM-DD') = ':DT_INI_JOB' AND x.DT_FIM IS NULL)";
var res = [];
while ((m = re.exec(str)) !== null) {
  res.push(m[1]);
}
document.body.innerHTML = "<pre>" + JSON.stringify(res, 0, 4) + "</pre>";

edited May 23 '17 at 10:28

Community

1
1

answered Jul 14 '16 at 14:21

Wiktor Stribiżew

607,720
39
448
563

if i dont use the slash('\') , it trows an error. Uncaught SyntaxError: Unexpected token : i cannot change the string, it is hardcoded from server... – Leonel Matias Domingos Jul 14 '16 at 14:35
What do you mean? You do not need to escape any `'` – Wiktor Stribiżew Jul 14 '16 at 14:38
@LeonelMatiasDomingos He isn't telling you to change the string, he's just showing you how to parse it. The string isn't relevant. You can surround it in double quotes though just so you can demo it without errors. – 4castle Jul 14 '16 at 14:43
@LeonelMatiasDomingos: your code is not valid JS here: `var str = 'AND DT_FIM IS NULL AND ( CD_JOB, DT_INI_JOB ) IN (SELECT x.CD_JOB, x.DT_INI FROM PRS_JOBS_MAQUINA x WHERE x.CD_JOB = ':CD_JOB' AND TO_CHAR(x.DT_INI, 'YYYY-MM-DD') = ':DT_INI_JOB' AND x.DT_FIM IS NULL)';`. You can't have it in your code. Fix it before applying any regex. – Wiktor Stribiżew Jul 14 '16 at 14:44

score 0 · Answer 2 · edited May 23 '17 at 12:26

0

Since it looks like you're binding the fields, you probably want to use replace with a replacement function. Use the regex from @WiktorStribiżew, /':([^']+)'/g, but then use it like this:

var sql = "AND DT_FIM IS NULL AND ( CD_JOB, DT_INI_JOB ) IN  (SELECT x.CD_JOB, x.DT_INI FROM PRS_JOBS_MAQUINA x  WHERE x.CD_JOB = ':CD_JOB' AND TO_CHAR(x.DT_INI, 'YYYY-MM-DD') = ':DT_INI_JOB' AND x.DT_FIM IS NULL)";

var fields = {
    CD_JOB: 1,
    DT_INI_JOB: 2
};
sql = sql.replace(/':([^']+)'/g, function($0, $1) {
  return fields[$1];
});
console.log(sql);

edited May 23 '17 at 12:26

Community

1
1

answered Jul 14 '16 at 14:36

4castle

32,613
11
69
106

this is perfect @WiktorStribiżew, but really need an array with the name of the binded fields.... it fine with var re = /':([^']+)/g; var str = "AND DT_FIM IS NULL AND ( CD_JOB, DT_INI_JOB ) IN (SELECT x.CD_JOB, x.DT_INI FROM PRS_JOBS_MAQUINA x WHERE x.CD_JOB = ':CD_JOB' AND TO_CHAR(x.DT_INI, 'YYYY-MM-DD') = ':DT_INI_JOB' AND x.DT_FIM IS NULL)"; var res = []; while ((m = re.exec(str)) !== null) { res.push(m[1]); } document.body.innerHTML = "
```
" + JSON.stringify(res, 0, 4) + "
```
";
– Leonel Matias Domingos Jul 14 '16 at 14:41
What are you using the array for? – 4castle Jul 14 '16 at 14:47

Substring and regex

2 Answers2