Extract a number from nvarchar column
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&lt;>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&lt;>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 &amp; 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&lt;>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&lt;>Fiddle