SQL Server – Use substring in where clause

I’m trying to come up with a query in which it’s going to filter based on a substring. The substring operation is working fine if I use it as a select statement, but not when I try to use it for filtering

-- These are some examples of the data I have
--cust_12
--cust_123
--temp_db
SELECT SUBSTRING(name,8,LEN(name)) AS ClientId, name FROM sys.databases
WHERE SUBSTRING(name,8,LEN(name)) IN ('12, 123');

​x
 
-- These are some examples of the data I have--cust_12--cust_123--temp_dbSELECT SUBSTRING(name,8,LEN(name)) AS ClientId, name FROM sys.databasesWHERE SUBSTRING(name,8,LEN(name)) IN ('12, 123');​

If I run the query without the where section, the data is parsed correctly, however when using the where I got no results

Answer

Is this the logic you want?

WHERE SUBSTRING(name, 8, LEN(name)) IN ('12', '123')

 
WHERE SUBSTRING(name, 8, LEN(name)) IN ('12', '123')​

But I think I would be more inclined to write this as:

where name like '%[_]12'] or
      name like '%[_]123'

 
where name like '%[_]12'] or      name like '%[_]123' ​

I just think the intent is clearer.

Advertisement

Answer