Extract last character of a variable for a SQL filter

Grafana Dashboards
, ,
1

Hi,

I have a variable called “Maquina” that has values: S1,S2,S3…
We have a graph that has a SQL source where the filter is going to be this “Maquina” variable. However, we want only the number: 1,2,3. How do we extract the last character of the variable.

I tried multiple formulas but non worked.

WHERE
($__timeFilter(fecha_inicio) or $__timeFilter(fecha_fin)) AND CodMaqui = strings.substring($Maquina, start:2, end:2)

Thanks beforehand

2

Hi @jmu

I am not sure but if I understood you correctly, those variables are defined in the Grafana dashboard ?

If that is the case, then simply use the regex when setting up this variable. Here is a reference link to the documentation.

You probably have to play around with it to make it working but it should give you your desired results.

3

i believe you have to make your column transformation in the select not in where,
like that :

select right($Maquina, length ) as char_maquina , CodMaqui from table
where
($__timeFilter(fecha_inicio) or $__timeFilter(fecha_fin)) AND CodMaqui = right($Maquina, length )

4

is this microsoft sql server? why does the variable Maquina have the values with S in the first place? Fix it there would be my recommendation. But we would need to see what it is that builds the Maquina query.

you could do this but does not address the root issue.

replace('S1,S2,S3','S','')

there are great pitfalls in string manipulation that will (not might) come to bite you later on

1 Like
5

Finally I get it what i want.
This is the code:

CodMaqui = SUBSTRING(${Maquina:sqlstring},2,1)

This way, the variable that has the value “S1” for example, i only get the last character which is the 1 and compare it in the database.