CODEWITHSUNDEEP
sql-serversql-server-2008
Prasanna Kumar J
Prasanna Kumar J

Reputation: 1598

How to get Sub string Using Sql Server 2008?

I am user sql server 2008 i need sub string between two character.That is the String are

String 
ABCD/23/L19
ABD/3/L12
BCD/23/L15
WBCD/54/L12
ABCD/8/L15

And My Result would be like this.

Desired_String
23
3
23
54
8

in this case i want string between '/'.Thanks in Advance.

Upvotes: 0

Views: 63

Answers (4)

Ullas
Ullas

Reputation: 11556

Use a combination of SUBSTRING and CHARINDEX.

Query

select string, substring(string, charindex('/', string, 1) + 1, 
          (len(string) - charindex('/', string, 1) - 
          charindex('/', reverse(string), 1))) as num
from your_table_name;

Demo here

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT SUBSTRING(t.string, t1.i + 1, t2.i - t1.i - 1)
FROM mytable AS t
OUTER APPLY (SELECT CHARINDEX('/', t.string)) AS t1(i)
OUTER APPLY (SELECT CHARINDEX('/', t.string, t1.i + 1)) AS t2(i)

Demo here

Upvotes: 2

Thangadurai.B
Thangadurai.B

Reputation: 561

Try this,

DECLARE @STRING VARCHAR(50)='WBCD/54/L12'
SELECT @STRING=RIGHT(@STRING,CHARINDEX('/',@STRING)+1)
SELECT LEFT(@STRING,CHARINDEX('/',@STRING)-1)

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Another way of doing

DECLARE @X VARCHAR(50)='WBCD/54/L12';

SELECT SUBSTRING(@X, CHARINDEX('/', @X, 1) + 1, CHARINDEX('/', @X, CHARINDEX('/', @X, 1) + 1) - CHARINDEX('/', @X, 1) - 1)

Upvotes: 1

Related Questions