Search for the numeric data from a full string SQL row


I’m having troubles trying to get only the numeric value inside of a full string row in a SQL table,

i.e “Conveyor 357 Jammed”. I’m trying to return only the 357 numeric value, there is a function to create this UDF in SQL studio, but is there any other way to make that easier in Ignition?

Studio SQL example:

CREATE FUNCTION dbo.udf_GetNumeric
@strAlphaNumeric VARCHAR(256)
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric)
WHILE @intAlpha > 0
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, ‘’ )
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric )
RETURN ISNULL(@strAlphaNumeric,0)

Can you edit the question (pencil icon), select the code and press the </> to apply code formatting to the SQL? I suspect it will make it a lot easier to read if your SQL is indented properly.

Regarding the actual question, what do you mean by “a full string row”? I would expect a particular column might be a string.

There is a string row i.e “Conveyor 357 Jammed”, and I’m trying to pull out the 357 numeric data from that string.

That’s just repeating what you’ve written in the original question. You haven’t explained what a “string row” is (and you haven’t fixed your code formatting). Make it easy for people to help you!

1 Like

I assume that your quarry is a scalar query returning only a string value?

If that is the case and the string will always be formatted as you have shown then you could do this in a script with the str.split() function or in an expression with the split() expression function.


num = int(value.split(‘ ‘)[1])


toInt(split({value},’ ‘)[1])

NOTE: I replied from my phone so most likely the quotes will be incorrect. Will probably be better off typing the code in yourself.

1 Like

A regex is generally considered the normative way to do it.

import re
pattern = '\d+'

stringIn = 'Conveyor 357 Jammed'

print [int(item) for item in re.findall(pattern, stringIn)]

stringIn = "he33llo this 42 is a 32 test30"

print [int(item) for item in re.findall(pattern, stringIn)]


[33, 42, 32, 30]

Sorry, ignore the code I used that code outside of Ignition in Studio and worked okay, but I was trying to find a better way to do it inside Ignition, the answer from @JordanCClark was the solution, thank you!

Thank you for your answer @JordanCClark that was the solution, I hope this post helps in the future for someone else!

I found the solution in @JordanCClark comment, but I’ll try your method as well, thank you!