KyLim KyLim - 1 month ago 11
SQL Question

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

Answer

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.