SWQL Single Character Wildcard / String Parsing

I don't believe that SWQL currently supports a single wildcard character.

See Orion Platform SWQL, Thwack! Now I got it.

Try an underscore.

Found a way to break apart the octets, but don't see a way to convert them into integers for the ORDER BY  ...

SELECT N.IPAddress AS IP

     , N.DnsBackward

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),0) AS O1

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),1) AS O2

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),2) AS O3

     , ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),3) AS O4

FROM IPAM.IPNode AS N

WHERE IP IS NOT NULL

ORDER BY ???

Any ideas?

Underscore does appear to work as a single wild card.

codebender​

Try using % as a wildcard. It's been a while since I've created a SWQL query, but I used that for some wildcard purposes.

tdanner had suggested in the past that you can do 0+yourvalue to get around the lack of casting in SWQL

SWQL Query Integer Conversion

Percent is a multi-character wild card that is documented Use SolarWinds Query Language (SWQL) in the Orion Platform - SolarWinds Worldwide, LLC. Help and Support.  It appears that, although undocumented, underscore '_' works as a single character like other SQL syntax.

The question was more general.  It appears that the only thing a string can be converted it (aside from other strings) is a date.

Without string manipulation syntax, how do people parse strings in SWQL?

Perhaps that functionality has been removed or I don't understand the suggested syntax.

When I do the following, I get an error: Failed to compare two elements in the array.

SELECT N.IPAddress AS IP

     , N.DnsBackward

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),0) AS O1

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),1) AS O2

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),2) AS O3

     , 0 + ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(IP,'.','|§|§|')),3) AS O4

FROM IPAM.IPNode AS N

WHERE IP IS NOT NULL

ORDER BY O1, O2, O3, O4

Ah yeah, i can imagine that stacking those functions could give you problems since it would seem like you are trying to do math against multiple objects in the array.  I don't normally use any of those array functions in SWQL. 

Might be able to nest your whole array and subnet deal in a subquery and then do the 0+ and order by stuff in the outer query, but thats just a guess

Normally if I can't do it in swql I wrap the whole query up in powershell and do my manipulations and parsing there.