Reputation: 993
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
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
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
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