FMS Query to get numeric value from alphanumeric field

[Former Member]

Hi,

I have an alphanumeric UDF like this: NETT1000

I would like to get "1000" only from this field?

[former_member184146 · Accepted solution]

Hi John,

to get the numeric value from alphanumeric field first you need to create the function

use the below function code

CREATE FUNCTION Numeric_from_string

(@alphaNumeric VARCHAR(256))

RETURNS VARCHAR(256)

AS

BEGIN

DECLARE @intalpha int

SET @intalpha = PATINDEX('%[^0-9]%', @alphaNumeric)

BEGIN

WHILE @intalpha > 0

BEGIN

SET @alphaNumeric = STUFF(@alphaNumeric, @intalpha, 1, '' )

SET @intalpha = PATINDEX('%[^0-9]%', @alphaNumeric )

END

END

RETURN ISNULL(@alphaNumeric,0)

END

after creating the above function use this function in your fms

SELECT DBO.Numeric_from_string($[Table_name.udf]) as Number

i.e.

SELECT DBO.Numeric_from_string('NETT1000') as Number 

this will return only 1000 .

Hope it help you.

--Manish

[frank_wang6 · Accepted solution]

a more commonly used way is to use Regular expression in SQL

https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-exa...

So far this is the easiest way to implement Regular expression I have ever seen.

Frank

[former_member184146]

try this

SELECT DBO.Numeric_from_string(t0.U_BarPrice) as Number  FROM OITM T0 WHERE T0.[ItemCode] = $[$23.1.0]

below in bold was issue alias of table

SELECT DBO.Numeric_from_string(OITM.U_BarPrice) as Number  FROM OITM T0 WHERE T0.[ItemCode] = $[$23.1.0]

--Manish