0

I have a table like below. Col1 values has parenthesis like val1(12). But when I write a InfluxQL query I want to remove parenthesis and just get remaining. When the InfluxQL query runs in the output Val1(12) will be Val1

mytable:

Col1 Col2 Col3 Col4
val1(12) 332 0 1
val2(4234) 222 0 1
val3(221) 111 0 1

If i write select * from mytable it wil give as below :

Col1 Col2 Col3 Col4
val1(12) 332 0 1
val2(4234) 222 0 1
val3(221) 111 0 1

But i want the paranthesis to be removed after i run the sql like below :

Col1 Col2 Col3 Col4
val1 332 0 1
val2 222 0 1
val3 111 0 1

I couldnt find a solution for this. Should i use trim or wildcard or regex to do this? InfluxDB shell version is 1.7.6. We will run this influxql in grafana dashboard.

Jan Garaj
  • 25,598
  • 3
  • 38
  • 59
ivbtar
  • 799
  • 11
  • 29
  • Please confirm you are actually using InfluxDB as opposed to any other SQL RDBMS. Are you running InfluxDB on-prem or their _Cloud_ version? – Dai Dec 02 '21 at 23:31
  • Apparently InfluxDB *does not use ISO SQL* - instead it has its own SQL-like language ( https://docs.influxdata.com/influxdb/v1.8/query_language/ ) - so we cannot really answer your question until you give more details. – Dai Dec 02 '21 at 23:32
  • We can run SQL queries in influx> console in linux. We also use grafana to run our queries to create dashboards. InfluxDB is datasource for our Grafana Dashboard. Even when i type influx in linux shell i can enter influx> console and run sql selects. But i will run SQL query in Grafana. – ivbtar Dec 02 '21 at 23:38
  • InfluxDB shell version is 1.7.6. so we can run sql in influxdb console – ivbtar Dec 02 '21 at 23:55
  • 1
    I'm not disputing that InfluxDB's language is _a form_ of SQL (or rather: _is SQL-like_, but it _isn't_ ISO SQL, and the distinction can be important. – Dai Dec 03 '21 at 00:10
  • Can you search for the position of the open parenthesis and then use that index to extract from the left? Perhaps concatenate a dummy paren at the end if matches are optional so that it's easier to guarantee a match. – shawnt00 Dec 03 '21 at 00:15
  • It is InfluxQL, not SQL. Also InfluxDB doesn't have columns, but fields and tags. I would recommend to read InfluxDB doc to know more. You will find there also that InfluxQL doesn't have any functions for string modification. You may try upgrade your InfluxDB and use Flux (another InfluxDB query language and it is absolutely not like a SQL, so you won't be confused it with SQL) – Jan Garaj Dec 03 '21 at 08:48
  • I see. My problem is i have thousands of field values written with () like value1(4324), value2(4234)...value1231(42342). These are written by another tool accidentally. I have to correct them to value1, value2...value1231 by removing paranthesis. How can i do this in influxDB version 1.7.6 – ivbtar Dec 03 '21 at 13:21

1 Answers1

1

I think you'll want to make use of SUBSTRING.

e.g.

SELECT SUBSTRING(Col1,0 CHARINDEX('(',Col1)), Col2, Col3
FROM MyTable
L.Newell
  • 102
  • 1
  • 2
  • 13