CODEWITHSUNDEEP
sqlsql-server
oldsport
oldsport

Reputation: 993

How to get a specific part from a string in SQL

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

Upvotes: 0

Views: 639

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 1

S3S
S3S

Reputation: 25152

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

Upvotes: 3

Related Questions