0

I have a table, in this table there is a column called parent_data, where there is a line with several characters and in the middle of them a number.

Exemple:

|PARENT_DATA|
{"orderERPNumber":1093710,"orderERPPosition":"P"}

I wanna know if there's a way I can do a query where I extract only the numbers.

The basic query:

SELECT PARENT_DATA FROM IONV_SYNC.IONVS_CRITICAS;

RETURNS:

{"orderERPNumber":1093710,"orderERPPosition":"P"}

So i want it to return only the number 1093710.

JNevill
  • 46,980
  • 4
  • 38
  • 63
Ryan Gabriel
  • 1
  • 1
    You would use `regexp_replace` as [asked and answered here](https://stackoverflow.com/questions/32861256/extract-number-from-string-with-oracle-function) – Isolated Aug 16 '23 at 13:40

2 Answers2

1

That looks like JSON to me. In which case you should use JSON functions to get the value out:

SELECT json_query('{"orderERPNumber":1093710,"orderERPPosition":"P"}', '$.orderERPNumber') FROM dual;

More information here on json paths (the $.orderERPNumber expression I used) and whatnot.

JNevill
  • 46,980
  • 4
  • 38
  • 63
0

Create a function for extracting numbers from string

CREATE FUNCTION dbo.GetNumericFromString
(
  @stringValue VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @intValue INT
  SET @intValue = PATINDEX('%[^0-9]%', @stringValue)
  BEGIN
    WHILE @intValue > 0
    BEGIN
      SET @stringValue = STUFF(@stringValue, @intValue, 1, '' )
      SET @intValue = PATINDEX('%[^0-9]%', @stringValue )
    END
  END
  RETURN LEN(COALESCE(TRIM(CAST(ISNULL(@stringValue, 0) AS INT)),0))>0 then COALESCE(TRIM(CAST(ISNULL(@stringValue, 0) AS INT)),0) else 0 end
END
GO

Then :

SELECT dbo.GetNumericFromString(PARENT_DATA) FROM IONV_SYNC.IONVS_CRITICAS;
Manoj
  • 11
  • 1