Extract a number from nvarchar column

oracle

Hoping you can help me. I have a column in a table called indicator. (Data type: NVarchar(80))

Within this column I have some numbers the length of this number is 6 characters in length. The total the Length of indicator column can vary from 0 to 56 Characters long.

Below is small Subset of the data. What I would like to do is produce another column which extracts the numbers from indicator column.

Row 1 : GB-JP--123456
Row 2 : 401015 - GB-NA
Row 3 :
Row 4 : 999999 - LX - UK

I tried the following query but I get this error

select Substring (Indicator, PATINDEX('%[0-9]%', Indicator), LEN(Indicator)) from Table1

However I am presented with the following error

"SUBSTRING": invalid identifier"

Best Answer

However I am presented with the following error

"SUBSTRING": invalid identifier"

The provided query should be able to run, here is a test on SQL Server 2017

Unless your tag is wrong and you are using oracle?

Then you would get an error like this:

ORA-00904: "SUBSTRING": invalid identifier

DB<>Fiddle

If that is the case, then you could use regexp_replace instead.

select regexp_replace(indicator, '[^0-9]', '') from table1

Resulting in

123456
401015

999999

DB<>Fiddle

Solution for SQL Server

Below is small Subset of the data. What I would like to do is produce another column which extracts the numbers from indicator column.

If you want to get the numbers from the column and they are always one series of continuous numbers, you could do this 

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING (Indicator, PATINDEX('%[0-9]%', Indicator), LEN(Indicator) )), PATINDEX('%[0-9]%', REVERSE(Indicator)), LEN(Indicator) ))
FROM table;

Table & Data 

create table #temp
(
indicator nvarchar(80)
);

INSERT INTO #temp
VALUES
('GB-JP--123456'),
('401015 - GB-NA'),
('1'),
('999999 - LX - UK');

Result

(No column name)
123456
401015
1
999999

DB<>Fiddle

Remember that due to using the LEN() function, trailing spaces are a potential issue.

INSERT INTO #temp
('1   ')

Returns an empty string when using above query.

DATALENGTH() can resolve this (divided by 2 due to the nvarchar datatype, not that it really matters when using SUBSTRING like this)

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING (Indicator, PATINDEX('%[0-9]%', Indicator),( DATALENGTH(Indicator)/2))), PATINDEX('%[0-9]%', REVERSE(Indicator)),( DATALENGTH(Indicator)/2) ))
from #temp;

Comparison DB<>Fiddle

Related Question