REPLACE function for SWQL?

Anything available in SWQL that would approximate the REPLACE function in SQL?

It's a long story, but this is the query I'm working with in SQL - basically a volume can fill up, but as long as all the same "opt<x>" volumes on a server don't fill up, it doesn't need to alert.

The SQL works in a query and report, but I want to get a dashboard resource going with SWQL - but the REPLACE functionality doesn't work to allow me to replace the various digits on the various lengths of volume name.

select Server, Volume, CAST(ROUND(Percentage,0) as varchar(10)) + ' %' as Percentage,CASEWHEN Used>=1099511627776THEN CAST(CAST( ROUND((Used/1024/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' TB'ELSE CAST(CAST( ROUND((Used/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' GB'END as Used,CASEWHEN Size>=1099511627776THEN CAST(CAST( ROUND((Size/1024/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' TB'ELSE CAST(CAST( ROUND((Size/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' GB'ENDas Sizefrom(select Server, Volume, SUM(VolumeSpaceUsed) as Used, SUM(VolumeSize) as Size, (SUM(VolumeSpaceUsed) / SUM(VolumeSize)) * 100 as Percentagefrom(select n.Caption as server,CASEWHEN v.Caption like '/opt%'and v.Caption<>'/opt' THEN 'opt'WHEN v.Caption like '%opt%'and v.Caption not like '/opt%'THEN     REPLACE(      REPLACE(        REPLACE(          REPLACE(            REPLACE(              REPLACE(                REPLACE(                  REPLACE(                    REPLACE(                      REPLACE(                        v.caption,                      '9', ''),                    '8', ''),                  '7', ''),                '6', ''),              '5', ''),            '4', ''),          '3', ''),        '2', ''),      '10', ''),    '1', '')ELSE v.CaptionEND as Volume, VolumeSpaceUsed, VolumeSize from Volumes vJOIN Nodes non n.NodeID=v.NodeIDwhere v.Caption like '%opt%'and v.Caption<>'/opt'and v.SilencedVolume<>1) xGROUP by Server, Volume) zwhere Percentage>=85Order by Percentage DESC, Server

Find more posts tagged with

replace

function

sql

dashboard

SWQL

custom resource

Accepted answers

tdanner

REPLACE is implemented in NPM 12.2 and the other Orion Platform 2017.3 releases (currently in beta).

All comments

tdanner

We don't currently have a REPLACE-equivalent function in SWQL, and unfortunately I don't see a way to accomplish this without one.

mwb

Well, technically CASE can be used to replace the volume names - it just won't have the dynamic properties in the same way.

I'm already doing it with volume that I know are /opt<x> - it's just not as easy with volumes that may be /<x>opt<x> - because the first variable can be random strings of characters.

So multiple CASE can certainly do it with a listing of all the possible prefix variables - it just won't update if there are new ones in the future.

That may be the way that I go, I'll update here if I find another solution.

Any thoughts as to add some more of these types of basic SQL functions to SWQL? SWQL is very powerful and useful, but frustrating when I have to design a whole new query to work around the limitations such as no REPLACE, no PIVOT, etc - which really are still read and display functions when you get down to it.

tdanner

REPLACE is a very obvious one to add. I'll open a bug for that.

PIVOT is a pretty complex SQL feature that changes the structure of the result set. It would definitely be useful, but it would take significant effort to add it to SWQL.

mwb

Thanks!

Yeah, PIVOT I've worked around with many multiple joins.

This REPLACE I've worked around the worst offenders with about half dozen specific WHEN statements in my CASE function.

Thanks for the quick feedback and seeing the value of the REPLACE. Especially since you're formatting data for dashboards in those Orion Custom Query resources, I can see where it could be used in multiple ways.

m-milligan

Did REPLACE ever get added to SWQL? It doesn't work for me.