Extract a number from nvarchar column

Question

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"

Answer 1

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

Answer 2

In Oracle you can use the TRANSLATE function

select TRANSLATE(Indicator, '0123456789'||Indicator,'0123456789')
    from Table1

Digits are translated to digits, other characters of the column are removed.

From Oracle SQL Language Reference

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in expr, then they are removed from the return value.

If a character appears multiple times in from_string, then the to_string mapping corresponding to the first occurrence is used.

Answer 3

Thanks for your help. Found i could do this way.

 SELECT 
indicator,
regexp_replace(indicator,'[^0-9]') as Numbers,
       regexp_replace(indicator, '[^a-z and ^A-Z]') as Characters
from temp