SQL Filter numbers between x and x and ignore strings

I have a table in SQL Server where I need to filter rooms by name and type. The problem is, that the names are stored as varchar and there are also some rooms with letters. I need to filter out the rooms with letters before I can compare them as int or otherwhise I will get an error.

varchar

int

Here's an example from Room.Name:

030
210a
210b
Lan-Room-A
240


I can work around the room names with a or b with LEFT(Rooms.Name, 3) but if I want to add (LEFT(Rooms.Name, 3) BETWEEN 0 and 350 and it gets to Lan-Room-A it oviously can't convert a string to int. I also need to do additional filtering like Room.Type = 6 for example.

a

b

LEFT(Rooms.Name, 3)

(LEFT(Rooms.Name, 3) BETWEEN 0 and 350

Lan-Room-A

Room.Type = 6

SELECT
Room.Name,
Room.Descr,
Room.MainUser
WHERE
LEFT(Room.Name, 1) NOT LIKE '%[0-9]%'
AND LEFT(Room.Name, 3) BETWEEN 0 AND 350
AND Room.Type = 6


(Removed some joins for simplicity)

I simply need to filter out the rows which contain strings before the when clause, but I have no idea how.

Do you guys have any idea?

Please note that I can't edit the database.

Thanks in advance.

3 Answers
3

I don't understand the issue. You can use strings:

WHERE Room.Name NOT LIKE '[0-9]%' AND
LEFT(Room.Name, 3) BETWEEN '0' AND '350' AND
Room.Type = 6


However, I suspect your intention is captured by:

WHERE Room.Name >= '000' AND
Room.Name < '351' AND
Room.Type = 6


This works because you have zero-padded the numbers, so string comparisons will work.

You could use TRY_CAST:

TRY_CAST

SELECT *
FROM Rooms
WHERE TRY_CAST(LEFT(Rooms.Name, 3) AS INT) BETWEEN 0 and 350;


DBFiddle Demo

Your second approach is not guaranteed to work even with correct check:

WHERE LEFT(Room.Name, 1) NOT LIKE '%[0-9]%'
AND LEFT(Room.Name, 3) BETWEEN 0 AND 350
and Room.Type = 6


Query optimizer could check conditions in any order so LEFT(Room.Name, 3) BETWEEN 0 AND 350 could yield conversion error.

LEFT(Room.Name, 3) BETWEEN 0 AND 350

TRY_CAST does not work before MSSQL 2012
– SnakeFoot
Jun 29 at 10:04

@SnakeFoot Yes, you are right. Please also note that SQL Server 2008 extended support will end on July 9 2019.
– Lukasz Szozda
Jun 29 at 10:07

thnx. nice to know as my office server is still on 2008
– SnakeFoot
Jun 29 at 10:11

Thanks for the quick reply, but sadly its MSSQL 2008 (for now). I will try it next week when we switch to a MS SQL 2016. If it works, I'll mark it as the answer.
– OverClockedE
Jun 29 at 10:13

try this :stackoverflow.com/questions/17941035/…
– SnakeFoot
Jun 29 at 10:19

For 2008. Please use this.

Solution-

;WITH CTE AS
(
SELECT '030' a UNION ALL
SELECT '210a' UNION ALL
SELECT '210b' UNION ALL
SELECT 'Lan-Room-A' UNION ALL
SELECT '240'
)
SELECT * FROM CTE
WHERE
PATINDEX('%[0-9]%', a) = 1 AND
1 = CASE WHEN CAST(LEFT(a, 3) AS INT) BETWEEN 0 and 350 THEN 1 ELSE 0 END


OUTPUT

a
----------
030
210a
210b
240

(4 rows affected)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.