Javascript question

General
1

I am trying to extract comma separators from excel if condition, my question is if my approach is adequate or if there’s a better way to do so?
it’ll be very nice if it works with multiple recursive if conditions.

=if(match(“CELL1”, “00002*”) and match(“CELL2”, “0005*”), ‘TEXT1’, ‘TEXT2’)

var c = `=if(match("CELL1", "00002*") and match("CELL2", "0005*"), 'TEXT1', 'TEXT2')`;

function getParameterPlace(formula) {
	var pos = [];
	var original_formula = formula;
	var len = 0;
	formula.replace(/\=if\s*?\((.*?)[^\)]*$/gi, function(a,b) {
		var len = a.length - b.length
		formula = b.substring(0, b.length-1);
		formula = formula.replace(/\((.*?)\)/g, function(c,d) {
			return 'X'.repeat(c.length)
		});
		var thenPOS = formula.indexOf(',') + len;
		var elsePOS = formula.lastIndexOf(',') + len;
		pos = [thenPOS, elsePOS];
	});
	return pos;
}

console.log(getParameterPlace(c))
2

Javascript I’m terrible, but I’m very good at Excel, I just didn’t understand your question.
Here in Brazil, Excel uses some different nomenclatures, but the IF formula operates as follows
=if(condition, value if true, value if false)
Of course there are combinations of formulas

Its syntaxes are:
=MATCH( lookup_value ; lookup_array ; [match_type] )
Lookup_value: Enter the value to be searched for, this will be the value that will be searched within the list selected in “lookup_array”;
Lookup_array: Select a column, row or matrix to search for the searched value;
[Match_type]:
1 = Finds the largest value less than or equal to the value sought;
0 = Finds the first value exactly equal to the value sought;
-1 = Finds the smallest value greater than or equal to the value sought;

It seems to me that you are wanting to create a system to refine searches, correct?

If I were to write this formula in Excel I would do it like this:
=if(e((match(“cell1”,"00002")),(match(“cell2”,"0005"))),‘text1’,‘text2’)**

However, the logic and use of conditions in the excel formula seem to be wrong.

1 Like
3

thank you @gilmar
I am looking to convert excel if condition to properly work in SQL script.
example

excel: if(CONCAT(“A1”, “A2”) = “ROB ZOMBIE”, ‘Y’, ‘N’)
SQL: CASE WHEN CONCAT(“A1”, “A2”) = ‘ROM ZOMBIE’ THEN ‘Y’ ELSE ‘N’

4

ChatGPT returned the following response for an example function I created in Excel.

Cells:
A1=1
A2=2
A3=3

Function:
=IF(CONCATENAR(A1;A2)="12";"yes";"no")

The answer to the if function in Excel was yes

function verificarCondicao(a1, a2) {
  if (a1 + a2 === "12") {
    return "sim";
  } else {
    return "não";
  }
}

// Exemplo de uso
var resultado = verificarCondicao("1", "2");
console.log(resultado); // Isso imprimirá "sim" no console
1 Like
5

hello @gilmar
Thanks again, I think I have not explained well, I am actually looking to convert excel syntax to sql syntax, for which I need to extract the proper comma positions for each “THEN” and “ELSE”, the issue is that commas can appear within other functions such as POWER(2,2) or CONCAT(‘A’, ‘B’) etc.
I hope I have explained well this time :slight_smile:

6

Unfortunately I don’t know how to help you, but someone here on the forum will certainly know, but why do you need this SQL?

1 Like
7

long story, I need to store data in sql database and I just have this idea that I want to work with. so… but thank @gilmar

1 Like
8

Hard to follow what you actually want to do, but JavaScript shouldn’t be used to access a database, better to use PHP.

4 Likes
9

I actually figured it out, thank you all for your time and efforts. basically using JavaScript

What I have:
if(CONCAT("A1", "A2") = "ROB ZOMBIE", 'Y', 'N')

What I want:
CASE WHEN CONCAT("A1", "A2") = 'ROB ZOMBIE' THEN 'Y' ELSE 'N' END