CODEWITHSUNDEEP
sqlsql-server
user3596633
user3596633

Reputation: 23

substring with variable last character SQL Server

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

Upvotes: 2

Views: 473

Answers (4)

user3596633
user3596633

Reputation: 23

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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)

Upvotes: 1

FDavidov
FDavidov

Reputation: 3675

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.

Upvotes: 0

S3S
S3S

Reputation: 25112

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

Upvotes: 0

Related Questions