How to get a specific part from a string in SQL

Question

I need to get a specific part from string.

In the following example the field POSITION contains the A- block, the M-0000000359 block and finally the block to the right of /.

What I need now is the full number to the right of / and if there is a , only the full number up to the comma.

So if the next output of POSITION would be A-M-0000000359/10 or A-M-0000000359/10,10 then the result I need now is 10 in both cases.

SQL

SELECT POSITION
  ,SUBSTRING((REPLACE(POSITION, SUBSTRING((POSITION), 1, CHARINDEX('/', (POSITION), 1)), '')), 1, CHARINDEX('/', (POSITION), 0)) AS TRIM_A
  ,SUBSTRING((REPLACE(POSITION, SUBSTRING((POSITION), 1, CHARINDEX('/', (POSITION), 1)), '')), 0, CHARINDEX(',', ((REPLACE(POSITION, SUBSTRING((POSITION), 1, CHARINDEX('/', (POSITION), 1)), ''))), 1)) AS TRIM_B
  ,*
FROM ORDER

Output

POSITION             |TRIM_A|TRIM_B
---------------------|------|------|
A-M-0000000359/1     |1
---------------------|------|------|
A-M-0000000359/1,10  |1,10   1

Your examples show what you want in fixed positions. Is this true in general? — Gordon Linoff
– Gordon Linoff, Commented Dec 21, 2016 at 15:47
The example (SQL & Output) is where I am stuck at the moment. The output you see works, if this does answer your question. — oldsport
– oldsport, Commented Dec 21, 2016 at 15:51
No, but how to expand the code, that I can guarantee to get the number to the right of the backslash, and if there is a comma, then only up the comma. — oldsport
– oldsport, Commented Dec 21, 2016 at 15:56

S3S · Accepted Answer · 2016-12-21 16:05:08Z

3

You can accomplish this with a CASE statement then. Change the @position variable to test it out.

declare @position varchar(64)= 'A-M-0000000359/1111,10'

select 
    case 
        when patindex('%,%',@position) > 0 
        then substring(substring(@position,CHARINDEX('/',@position) + 1,len(@position) - CHARINDEX('/',@position)),1,patindex('%,%',substring(@position,CHARINDEX('/',@position) + 1,len(@position) - CHARINDEX('/',@position))) - 1)
        else substring(@position,CHARINDEX('/',@position) + 1,len(@position) - CHARINDEX('/',@position))
    end

edited Dec 21, 2016 at 16:05

answered Dec 21, 2016 at 15:58

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

oldsport Over a year ago

Thx! But it gives me the last number only (10). But it should be based on your example 1.

S3S Over a year ago

fixed it since you want the first one. try now @oldsport

Gordon Linoff Over a year ago

Instead of patindex(), I would just use like.

S3S Over a year ago

Good idea @GordonLinoff

John Cappelletti · Accepted Answer · 2016-12-21 16:22:18Z

1

Perhaps a lighter alternative

Declare @YourTable table (Position varchar(50))
Insert Into @YourTable values
('A-M-0000000359/1,10'),
('A-M-0000000359/1'),
('A-M-0000000359')


Select A.*
      ,Trim_A = case when charindex('/',Position)=0 then '' else substring(Position,charindex('/',Position)+1,50) end
      ,Trim_B = case when charindex(',',Position)=0 then '' 
                     else substring(Position,charindex('/',Position)+1,charindex(',',Position)-charindex('/',Position)-1) 
                     end
 From  @YourTable A

Returns

Position             Trim_A   Trim_B
A-M-0000000359/1,10  1,10     1
A-M-0000000359/1     1  
A-M-0000000359

answered Dec 21, 2016 at 16:22

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

2 Comments

S3S Over a year ago

Kudos for taking the extra step in case there wasn't any /

John Cappelletti Over a year ago

@scsimon Just always shocked when the user comes back as says "This works, but I ALSO have this condition. 3 solutions for this guy, each time a another drop of information. stackoverflow.com/questions/41232461/…

Shushil Bohara · Accepted Answer · 2016-12-21 16:22:47Z

1

Can you please try this, I found it very simple and easy to understand that we can simply do it using CASE

create table #test(block varchar(50))

insert into #test values
('A-M-0000000359/10,11'), ('A-M-0000000359/10')

select substring(block, charindex('/', block)+1, 
    case when charindex(',', block) = 0 then 
        len(block) 
    else 
        (charindex(',', block)-1)-charindex('/', block) 
    end) finalValue
from #test

OUTPUT
----------
finalValue
10
10

answered Dec 21, 2016 at 16:22

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to get a specific part from a string in SQL

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related