CODEWITHSUNDEEP
sqlsql-server
KyLim
KyLim

Reputation: 476

SQL get character before and after '/'

DCR/170/06
DCR/001/07
DCR/1504/07

I want get the character between the '/' and then +1 my expected output as below:

SELECT MAX(RIGHT('000'+CAST(Doc_Num+1 AS VARCHAR(4)),4)) AS NEW_RESULT FROM Dms_Table

171
002
1505

Upvotes: 0

Views: 67

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

You could use ParseName()

Declare @String varchar(100) = 'DCR/170/06'

Select 1+ParseName(Replace(@String,'/','.'),2)

Returns

171

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Here is one method:

select cast(substring(doc_num, 5,
                      charindex('/', doc_num, 5)
                      ) as int) + 1

This particular version assumes that the 'DCR/' is how all the strings start.

You seem to understand how to left pad the value with zeros.

Upvotes: 2

Related Questions