# Search for the numeric data from a full string SQL row

**URL:** <https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661>\
**Category:** Ignition\
**Tags:** ignition81, scripting\
**Created:** [July 7, 2022, 10:29pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661 "2022-07-07T22:29:23Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\
**Post date:** [July 7, 2022, 10:29pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/1 "2022-07-07T22:29:23Z")

</div>

Hi,

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

```auto

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [July 7, 2022, 10:32pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/2 "2022-07-07T22:32:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [July 7, 2022, 10:34pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/3 "2022-07-07T22:34:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\
**Post date:** [July 7, 2022, 10:37pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/5 "2022-07-07T22:37:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [July 7, 2022, 10:39pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/6 "2022-07-07T22:39:42Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 7, 2022, 11:59pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/7 "2022-07-07T23:59:31Z")

</div>

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:

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

```

Expression:

```auto
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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 8, 2022, 12:32am UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/8 "2022-07-08T00:32:45Z")

</div>

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

```python
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)]

```

Output:

```auto
[357]
[33, 42, 32, 30]

```

---

<div class="post-metadata">

**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\
**Post date:** [July 8, 2022, 3:19pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/9 "2022-07-08T15:19:12Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\
**Post date:** [July 8, 2022, 3:20pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/10 "2022-07-08T15:20:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![a.beltran](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/a.beltran/32/18481_2.png) [@a.beltran](https://forum.inductiveautomation.com/u/a.beltran)\
**Post date:** [July 8, 2022, 3:20pm UTC](https://forum.inductiveautomation.com/t/search-for-the-numeric-data-from-a-full-string-sql-row/61661/11 "2022-07-08T15:20:56Z")

</div>

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