substring with variable last character SQL Server

Question

I have a number of rows in a database column that look like this

AB01-52
AB01-52/1
AB01-53/2
AB01-54

I can get the 52 where there is no slash in the string but when I try to get it between the dash and the slash I either get an error or get the 52/1 for example

I have tried most of the researched solutions using substring and charindex but to no avail

Has anyone seen a solution for this.

Thanks in advance

Rick

case when charindex('/'field1) = 0 then substring(field1,charindex('-',field1)+1 ,len(field1)) else charindex('/'field1) = 0 then substring(field1,charindex('-',field1)+1 ,len(field1)) else SUBSTRING(fiel1,CHARINDEX('-',field1)+1,CHARINDEX('/',field1)-CHARINDEX('-',field1)-1) — user3596633
– user3596633, Commented May 24, 2018 at 13:28
I also am unsure how your solutions with charindex/substring were to no avail. That is absolutely how you would solve your problem. — dfundako
– dfundako, Commented May 24, 2018 at 13:29
If all the values you wish to extract are the 6th and 7th characters in the string, this is easy to do. Are there more variations that need to be taken into account? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 24, 2018 at 13:31
Nope no variations there is either a slash followed by a number or there is no slash — user3596633
– user3596633, Commented May 24, 2018 at 13:33

Yogesh Sharma · Accepted Answer · 2018-05-24 13:44:09Z

1

You can subtract the characters positions (i.e. /) and use as length for substring()

substring(col, charindex('-', col)+1, len(col)-charindex('/', col)+1)

If the numbers have variable length then do the subtraction from both characters and use of length

substring(col, charindex('-', col)+1, charindex('/', col+'/')-charindex('-', col)-1)

edited May 24, 2018 at 13:44

answered May 24, 2018 at 13:27

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

Comments

S3S · Accepted Answer · 2018-05-24 13:28:24Z

0

Here's one approach....

with cte as(
select 'AB01-53/2' as C1
union
select 'AB01-54')

select left(right(c1,len(c1) - charindex('-',c1)),len(right(c1,len(c1) - charindex('-',c1))) - charindex('/',right(c1,len(c1) - charindex('-',c1)))+ 1)
from cte

answered May 24, 2018 at 13:28

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Comments

FDavidov · Accepted Answer · 2018-05-24 13:29:08Z

0

If the field is ALWAYS AAAA-BB[/CCC], then simply:

  SUNSTRING('AB01-52/1',6,2) ;

Of course, the 'AB01-52/1' may be substituted by a variable or column name.

answered May 24, 2018 at 13:29

FDavidov

3,7556 gold badges27 silver badges72 bronze badges

1 Comment

FDavidov Over a year ago

In that case, update your example so people (and me) can better understand what you need.

bfontaine · Accepted Answer · 2018-05-24 15:20:46Z

0

I may have found an answer but it involves a 2 stage process

First add an additional slash to the column that contains the data where there is no slash at the moment ie AB01-52 becomes AB01-52/ the others like AB01-1023/01 remain the same

stage 2 use this code:

substring(right(col1,len(col1)-charindex('-',col1)),0,charindex('/',right(col1,len(col1)-charindex('-',col1))))

this allows me to get the middle string. The use of left and right by various people who contributed helped to get to this - however I still thing there should be an answer without stage 1

Thanks you all for your ideas which got me to this point

edited May 24, 2018 at 15:20

bfontaine

21.3k15 gold badges88 silver badges127 bronze badges

answered May 24, 2018 at 15:03

user3596633

232 bronze badges

Collectives™ on Stack Overflow

substring with variable last character SQL Server

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related