SQL Server - Use substring in where clause

Question

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');

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

With your logic, the string "cust_12" is evaluated as '' - you start at the 8th character. — SMor
– SMor, Commented May 15, 2019 at 22:55

Gordon Linoff · Accepted Answer · 2019-05-15 22:53:37Z

4

Is this the logic you want?

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'

I just think the intent is clearer.

answered May 15, 2019 at 22:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

E.J. Brennan · Accepted Answer · 2019-05-15 22:55:22Z

1

Your quotes are wrong:

SELECT SUBSTRING(name,8,LEN(name)) AS ClientId, name FROM sys.databases
WHERE SUBSTRING(name,8,LEN(name)) IN ('12, 123');

should be:

SELECT SUBSTRING(name,8,LEN(name)) AS ClientId, name FROM sys.databases
WHERE SUBSTRING(name,8,LEN(name)) IN ('12', '123');

answered May 15, 2019 at 22:55

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

Collectives™ on Stack Overflow

SQL Server - Use substring in where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related