CODEWITHSUNDEEP
sqlsql-serverwhere-clause
OverClockedE
OverClockedE

Reputation: 23

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.

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.

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.

Upvotes: 2

Views: 2607

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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.

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2021

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)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

You could use 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.

Upvotes: 2

Related Questions