Search for a pattern using SQL query and then get the maximum

1

I have the following SQL query from SSMS and it is returning CompanyCase column values like

MH9090
MH9089
MH9088
MH9087
MH9086
MH9085

So as you can see, CompanyCase are in descending orders and in a sequence as far as numbers are concerned. Now, there can be a scenario when
some of them might not be in a sequence and it can be like the following:

MH10005
MH10001
MH9099
MH9090
MH9089
MH9088
MH9087
MH9086
MH9085

In such scenarios, I want to write a SQL query which can find out 3 or 5 sequential values and based on that I can warn the user about the next
sequentia value. In above example, since MH9085 - MH9090 are in sequential, I would like to suggest the user that you should use MH9091 as the next value
I think even if I can get MH9090 in this case, I can suggest by adding 1 to 9090 that MH9091 is the next CompanyCase which coud be used.
Is there a way to modify the below query to achieve the same? The datatype of CompanyCase column is varchar(16) in the Employee table.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [employeeID]
      ,[clientID]
      ,[speciesID]
      ,[CaseID]
      ,[CompanyCase]
      ,[entryDate]
      ,[lastUpdate]
      ,[checkInDate]
      ,[endDate]
  FROM [mydatabase].[dbo].[Employee]
  where CompanyCase LIKE 'MH %'
  ANd endDate IS NULL
  order by checkInDate desc, CompanyCase desc
2

hi

hope this helps

create data script

drop table #Data
create table #Data
(
CompanyCase varchar(16)
)

insert into #Data select 'MH10005'
insert into #Data select 'MH10001'
insert into #Data select 'MH9099'
insert into #Data select 'MH9090'
insert into #Data select 'MH9089'
insert into #Data select 'MH9088'
insert into #Data select 'MH9087'
insert into #Data select 'MH9086'
insert into #Data select 'MH9085'

select 
    max('MH'+ cast(cast ( replace(b.Companycase,'MH','') as int ) + 1 as varchar))
from 
   #Data a join #Data b 
        on replace(a.companycase,'MH','') + 1   = replace(b.companycase,'MH','')

image

3

Thanks. Can you explain it a little bit?

When I ran your query, I found some bad data which gave me an error, so how can ignore it?

Here's the error I got:

Msg 245, Level 16, State 1, Line 25
Conversion failed when converting the varchar value 'A /00000713' to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

So I am wondering should I modify your query to include something like WHERE NOT CompanyCase = 'A /00000713'

If yes, then how can I modify with WHERE cause?

4

where CompanyCase LIKE 'MH %'

add this to my SQL