I have lot of tags which look like...
I want to get two columns as a result, left and right of the sign :
How to do it with PI SQL Commander syntax?
Or this if you are trying to split tag names and not values...
LEFT(TagName, INSTR(TagName, ':') - 1),
RIGHT(TagName, LEN(TagName) - INSTR(TagName, ':'))
SELECT TAG AS TagName
WHERE tag LIKE '%:%'
Would the following be what you are after?
UPDATE YourTableSET Col2 = RIGHT(Col1,LEN(Col1)-CHARINDEX(':',Col1))
I tried to use function CHARINDEX in PI SQL Commander Lite but is not supported.
Thanks for pointing that out.
Taking a step back would be using string builder in AF to do the modification.
What's the end goal with the information?
Knowing the end goal will help us figure out what the best solution is for your problem.
The ultimate goal is grouping some calculation by destination string, right of :
Left side of string is name of input, and right side is output.
I want to know at the end which inputs go to the same output.
Give this a try:
LEFT(VALUE, INSTR(VALUE, ':') - 1),
RIGHT(VALUE, LEN(VALUE) - INSTR(VALUE, ':'))
SELECT TAG, CAST(VALUE AS STRING) AS VALUE
WHERE tag = 'StringTag'
AND time BETWEEN 't' AND '*'
Retrieving data ...