SQL Server 2008
SELECT ('ABC-' +
MAX(CAST(
(SUBSTRING([ID], CASE CHARINDEX('-', [ID])
WHEN 0
THEN LEN([ID]) + 1
ELSE CHARINDEX('-', [ID]) + 1
END, 1000)+1)
AS VARCHAR)
)) AS next_id
FROM [STUFF]
WHERE [FK_DATA] = '12345'
Table [STUFF] contains column [ID] with values like "ABC-1", "ABC-2". But the WHERE clause may result in 0 rows returned.
The query gets the next increment of the ABS's, e.g. "ABC-3". Except when there are no matching rows. Then I don't get any results.
It's part of a sub-query, so I need it to return 'ABC-1' instead of nothing. I.e. the inner part has to return a 1. (My outer query then inserts this newest increment.)