SUBSTRING() function behaving differently in SELECT and WHERE clause?

Question

I have a table with the following column:

server
SLQ-ABCD-001
SLQ-ABCA-002
SLP-DBMSA-003
SLD-ABC-004
SLS-123456-005

I would like to be able to filter the rows based on a substring of this column, specifically, the string between those hyphens; there will always be three characters before the first hyphen and three characters after the second hyphen.

Here's what I have tried:

AND substring(server, 5, (len(server)-8)) in ('ABC', 'DBMSA')
AND substring(server, charindex('-', server)+1,(charindex('-',server, charindex('-', server)+1)-(charindex('-', server)+1))) in ('ABC', 'DBMSA')

Both of these work perfectly fine (expected substrings obtained) when used in the SELECT clause but give the error below

Invalid length parameter passed to the LEFT or SUBSTRING function.

I am not able to use the more simpler way, AND server like '%ABC%' as I have more than one combination of characters I'm looking for and also, because that comma separated list will be dynamically parsed in that query for this use case.

Is there any way this type of filter can be achieved in SQL Server?

EDIT

After @DaleK helped me realize that the issue might be I might have some bad data (server names with length < 8) and that I might have missed them when I tested the expression in the SELECT clause since I might have some other filters in my WHERE clause, here's how I had managed to get around that

SELECT *
from
(SELECT *
from my_original_table
where
--all my other filters that helped me eliminate the bad data
) my_filtered_table
where substring(server, 5, (len(server)-8)) in ('ABC', 'DBMSA');

As for the "question being duplicate" part, I think the error in that question is encountered in SELECT statement where as in my case, the expression worked fine in the SELECT statement and only errored when used in the WHERE clause.

For solution, the one provided by @Isolated seems to work perfectly fine!

Just use a `case` to check that the length is long enough before you try and substring it. The thing about using calculations like this in a where clause is that SQL Server has to evaluate it for all rows, not just the ones returned, and you must have some data that breaks your logic hidden away. — Dale K, Oct 27 '22 at 20:37
Hi @DaleK, thank you for your quick response. I'm certain the length is not the problem as I have checked for any bad data in that column. However, if it's really necessary, what do you advise I check the minimum length to be? — Milin Patel, Oct 27 '22 at 20:45
The error suggests otherwise... clearly if you evaluate your logic on every row in your table, some fail. As I don't know what is failing I can't really suggest how you would check for it. You'll need to investigate. — Dale K, Oct 27 '22 at 20:47
If you're saying `len(server)-8` then you better be making sure `CASE WHEN len(server) >= 8` otherwise if `len(server) = 7` you're saying `substring(x,5,-1)` which is not valid. While later where clauses might eliminate bad data (e.g. `WHERE len(server) >= 8`) it's still possible that SQL Server will try and perform substring etc. _before_ it filters those out. It won't do that with a filter _and_ a `CASE` expression around any problematic calculation that relies on the filter. — Stuck at 1337, Oct 27 '22 at 20:52
Regardless its going to perform like a sack of turnips... the engine has no choice but to evaluate this against every row in the table because it can't use indexes when you put these sort of calculations in the where clause. — Dale K, Oct 27 '22 at 20:58
Anyway, if `server` is `varchar(255)`, just say `substring(server, 5, 255)`. There's no reason to cap `substring` at the _actual length_. — Stuck at 1337, Oct 27 '22 at 21:00
@DaleK I have checked for bad data by checking for len(server) >=8 that might not be enough. The reason is I only checked for the bad data while using some other filters I require in my query but SQL Server needs to evaluate every row. Am I understanding this right? — Milin Patel, Oct 27 '22 at 21:05
@MilinPatel If that's in your where clause, that's not checking for bad data, that's just preventing rows that contain bad data from being included in the output. Such rows can get evaluated elsewhere in the query _before_ they get eliminated via the where clause. — Stuck at 1337, Oct 27 '22 at 21:09
Seems largely a duplicate of [this question](https://stackoverflow.com/q/73138288/20091109) (and probably many others). — Stuck at 1337, Oct 27 '22 at 21:12

score 2 · Answer 1 · answered Oct 27 '22 at 20:47

One simpler approach you can try is the (often misused) parsename function:

Example being

with sampledata as (
  select * from (
   values('SLQ-ABCD-001'),('SLQ-ABCA-002'),('SLP-DBMSA-003'),('SLD-ABC-004'),('SLS-123456-005')
  )x([server])
)

select [server]
from sampledata
cross apply(values(Replace([server], '-','.')))v(v)
where ParseName(v,2) in ('ABC', 'DBMSA');

Isolated · Answer 2 · 2022-10-27T21:35:24.257

No need for substring. You could nest left and right with len such as this:

with my_data as (
  select 'SLQ-ABCD-001' as server union all
  select 'SLQ-ABCA-002' union all
  select 'SLP-DBMSA-003' union all
  select 'SLD-ABC-004' union all
  select 'SLS-123456-005'
  )
select server
from my_data
where left(right(server, len(server) - 4), len(right(server, len(server) - 4))- 4)  in ('ABC', 'DBMSA')

server
SLP-DBMSA-003
SLD-ABC-004

And left(right(server, len(server) - 4), len(right(server, len(server) - 4))- 4) works fine in the select clause too.

SUBSTRING() function behaving differently in SELECT and WHERE clause?

2 Answers2