Extra a number from nvarchar column

1

Good Morning Team,
Hope all is well

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"

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

you have a space after substring?

4 
use sqlteam
go

create table #masonry(Indicator nvarchar(80))

insert into #masonry
select 'GB-JP--123456' union
select '401015 - GB-NA' union
select '' union
select '999999 - LX - UK' 

SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(Indicator, pos, LEN(Indicator))
    FROM (
        SELECT Indicator, pos = PATINDEX('%[0-9]%', Indicator)
        FROM #masonry
    ) d
) t


drop table #masonry
5

hi

i tried to do this ... hope it helps ... :slight_smile: :slight_smile: ... i love any feedback

this is a different approach
using tally table

drop create data ... 
create table #masonry(Indicator nvarchar(80))

insert into #masonry
select 'GB-JP--123456' union
select '401015 - GB-NA' union
select '999999 - LX - UK'
SQL .. using tally table approach .. 
;WITH tally 
     AS (SELECT TOP (100) N=Row_number() 
                              OVER ( 
                                ORDER BY @@spid) 
         FROM   sys.all_columns), 
     data
     AS (SELECT Indicator, 
                col 
         FROM   #masonry 
                CROSS apply (SELECT (SELECT c + '' 
                                     FROM   (SELECT n, 
                                                    Substring(Indicator, n, 1) C 
                                             FROM   tally 
                                             WHERE  n <= Datalength(Indicator)) [1] 
                                     WHERE  c BETWEEN '0' AND '9' 
                                     ORDER  BY n 
                                     FOR xml path(''))) p (col) 
         WHERE  p.col IS NOT NULL) 
SELECT * from data
go

image

1 Like