Find exact numbers in a VARCHAR column

Transact-SQL
1

Hi everybody,

here comes a bloody beginner issue (after i searched the internet for almost two hours, tried with CAST and CONVERT and still seem to be miles away from a solution).

I´ve currently got the following query:

select * from perso where Ausweis BETWEEN '48650' AND '48699';

Might be notable: the column "Ausweis" is VARCHAR (that´s why i tried to CAST/CONVERT it - meanwhile i know that at least CONVERT is not the right thing for that).

When i execute the query it always gives me four-digit-results also, but i just need the five-digit entries.

query
query383×633 14.6 KB

I think i know that there´s a simple solution, but i can´t figure it out...

Thx
CPB

2

select * from perso where LEN(Ausweis) = 5 AND Ausweis BETWEEN '48650' AND '48699';

1 Like
3

Not sure but what is the issue with this?

SELECT * FROM perso WHERE CONVERT(INT,Ausweis) BETWEEN 48650 AND 48699;

If Ausweis isn't a number all the time you could try

;WITH PersonValidAusweis AS
(
SELECT * FROM Perso WHERE ISNUMERIC(ISNULL(perso,'Empty'))=1
)
SELECT * FROM perso WHERE CONVERT(INT,Ausweis) BETWEEN 48650 AND 48699;
1 Like
4

Thanks - that work immediately!

5

Rogier,

Fyi, you don't need the ISNULL in your cte:

SELECT * FROM Perso WHERE ISNUMERIC(perso)=1

1 Like
6

For what it's worth: try to avoid using functions on the left side of an equation in the criteria. It prevents the engine of using indexes and results in full table scans and slow queries.

1 Like
7

I'll also recommend that ISNUMERIC() does NOT work the same way as an IS_AllDigits function. ISNUMERIC() will return a 1 even for an entry with 5 commas in it.

Please see the following article for more information.

1 Like