How to get characters between 2 underscores?

Transact-SQL
1

Hi,
I'm looking for a help regarding getting characters between underscores.

I have a table called: MyTable
And I have a column called: supplierNumber

In the supplierNumber columns, I have the following values:
ML20_0052638271_D431
MB20_8362518462_D20
NB20_9073623527_RD20

What I'd like to get, is only the 10 digit numbers between the 2 underscores, so:
0052638271 for first example,
8362518462 for second example,
9073623527 for third example.

2

SELECT SUBSTRING(supplierNumber, CHARINDEX('', supplierNumber) + 1, CHARINDEX('', supplierNumber + '', CHARINDEX('', supplierNumber) + 1) - (CHARINDEX('_', supplierNumber) + 1))

1 Like
3

Damn formatting; I guess this site uses _ for some formatting. Replace US in the code below with an _.

SELECT SUBSTRING(supplierNumber, CHARINDEX('US', supplierNumber) + 1, CHARINDEX('US', supplierNumber + 'US', CHARINDEX('US', supplierNumber) + 1) - (CHARINDEX('US', supplierNumber) + 1))

1 Like
4

hi

hope this helps

another way of doing this ... was looking for a cleaner nicer way

this solution assumes that always you will have 5 characters starting
ML20_
MB20_

Not starting like this
MLSDFG_
MASDFWERT_

create sample data script

drop table if exists #Table
create table #Table ( supplierNumber varchar(50))
insert into #Table select 'ML20_0052638271_D431'
insert into #Table select 'MB20_8362518462_D20'
insert into #Table select 'NB20_9073623527_RD20'

select  
     LEFT(STUFF(supplierNumber, 1, 5,''),10)  
from 
     #Table

image

5

hi

hope this helps

another way of doing it

create data script

drop table if exists #MyTable
create table #MyTable ( supplierNumber varchar(50))
insert into #MyTable select 'ML20_0052638271_D431'
insert into #MyTable select 'MB20_8362518462_D20'
insert into #MyTable select 'NB20_9073623527_RD20'

select 
     value 
from 
   #MyTable 
        cross apply 
   string_split(supplierNumber,'_') 
where 
    value NOT LIKE '%[a-zA-Z]%'

image