Hi,
I have a variable $name.
Example of $name= AAABBB
I am trying to do regex and get first three characters of the $name variable (exmp: AAA) to use in mysql query.
I am trying something like this in query but it is not working…
{$name:^.{0,3}}
I would appreciate some feedback on this.
variable: name
name= AAABBB
If you trying use variable in query SQL and you want get first three characters, put in code:
SELECT left(’[[name]]’,3)
Thanks for your reply, I have created a custom variable $name.
I have included comma seperated values for the variable as AAAVVV, VVVEEE etc.
if i (select an option from dropdown list) like ‘AAAVVV’
SELECT UNIX_TIMESTAMP(time) AS 'time', '$name' FROM 'namedb' ORDER by time ASC
It works correctly and the query becomes…
SELECT UNIX_TIMESTAMP(time) AS 'time', 'AAAVVV' FROM 'namedb' ORDER by time ASC
But in my case I want to query to be as follow
SELECT UNIX_TIMESTAMP(time) AS 'time', 'AAA' FROM 'namedb' ORDER by time ASC
But not only in MySQL I am planning on using the variable that will get me the first 3 characters in other places.
I have created another variable called $three.
I am trying to do something like this
so whenever I use $three variable, it gets first 3characters from the $name
I am not sure if it can be done but just trying
try this:
SELECT UNIX_TIMESTAMP(time) AS ‘time’, left(’[[$name]]’,3) FROM ‘namedb’ ORDER by time ASC
or
SELECT UNIX_TIMESTAMP(time) AS ‘time’, left(’[[three]]’,3) FROM ‘namedb’ ORDER by time ASC
Actually I tried this
SELECT UNIX_TIMESTAMP(time) AS ‘time’, left(’[[name]]’,3) FROM ‘namedb’ ORDER by time ASC
It still gets the full name as AAAVVV
Maybe I was not clear…
I am not trying to query the Mysql to get the 3 letters of the column field…
I am trying to get first 3 letters of the variable ($name) I have created in Grafana and insert it to the Query or any other place.
I am adding variable three with two names: AAABBB and CCCDDD
and put in query and get first three characters, filter shows AAABBB but table only three first characters AAA
In my query when I use it, it does not select the column and list the values. But, it just prints
SELECT UNIX_TIMESTAMP(time) AS “time”, LEFT(’$three’, 3) AS NAME
FROM namedb
ORDER by time ASC
Time Name
yyyy-mm-dd AAA
yyyy-mm-dd AAA
maybe change visualization type
Thanks for replies again. I still couldnt solve my problem but what I found out is this if I use this
SELECT UNIX_TIMESTAMP(time) AS time, LEFT([[name]], 3) FROM namedb
I get the following
2020-09-04 02:00:00 AAA
2020-09-04 03:00:00 AAA
2020-09-04 04:00:00 AAA
2020-09-04 05:00:00 AAA
2020-09-04 06:00:00 AAA
2020-09-04 07:00:00 AAA
If I use the below code
SELECT UNIX_TIMESTAMP(time) AS time, AAA FROM namedb
I get the following result
2020-09-04 02:00:00 63
2020-09-04 03:00:00 63
2020-09-04 04:00:00 62
2020-09-04 05:00:00 62
2020-09-04 06:00:00 62
2020-09-04 07:00:00 60
I am trying to get the result of below code:
SELECT UNIX_TIMESTAMP(time) AS time, AAA FROM namedb
(“AAA” in above code is dynamic and pulled from first three characters of $name variable.)
I would really apriciate myabe some other solution, I have been obssesed past 24 hours with this problem.
Please help me.
Hi
You wrote, below query show you correctly data, why u don’t use this?
SELECT UNIX_TIMESTAMP(time) AS time, LEFT([[name]], 3) FROM namedb
result
2020-09-04 02:00:00 AAA
2020-09-04 03:00:00 AAA
Sorry, if I misunderstood. Maybe you paste some screens: variable, query, type of visualization
my database structure is as following
time-------------------------------AAA------------BBB
2020-09-04 02:00:00---------63---------------46
2020-09-04 03:00:00---------62---------------65
The following code
SELECT UNIX_TIMESTAMP(time) AS time, LEFT([[name]], 3) FROM namedb
OUTPUTS
2020-09-04 02:00:00 AAA
2020-09-04 03:00:00 AAA
But I need to modify the query so it outputs like below
2020-09-04 02:00:00 63
2020-09-04 03:00:00 63
Ok, I am understand, you must give:
Select SELECT UNIX_TIMESTAMP(time) AS time, ${name:csv} FROM namedb
but ${name:csv} must have name AAA, I dont know how cut first three characters
and you got:
2020-09-04 02:00:00 63
Thanks for your reply, what is this format ${name:csv}, where can I get documentation about it. I can search further you been great help.
or ${name}
without :csv
but csv give you multiple values
documentation:
Can I do the following,
I have the variable $name
Create a new variable which gets first 3 letters from $name variable, and use that inside query?
Please show how you did it. This will be helpful for others people