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)
)
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, ‘’ )
SET @intAlpha = PATINDEX(‘%[^0-9]%’, @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
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.
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!
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.
Script:
num = int(value.split(‘ ‘)[1])
Expression:
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.
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)]
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!