LIKE problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

LIKE problems

Author

Topic

yoyosh
Starting Member

27 Posts

Posted - 2013-01-07 : 03:40:40

I am solving excersies on sql-ex. I have problem with ex. 35:

In Product table, determine the models which consist only of digits or only of latin letters (A-Z, case insensitive).

Here is what I tried:

select model, type
from product
where
(ISNUMERIC(model) =1)
or
(model NOT LIKE '%[^a-z]%')

I am however getting the following error:
Your query produced correct result set on main database, but it failed test on second, checking database.

Could you please at least suggest me what may be wrong? Thank you in advance

yoyosh
Starting Member

27 Posts

Posted - 2013-01-07 : 03:46:13

EDIT: I am getting more records than expected

yoyosh
Starting Member

27 Posts

Posted - 2013-01-07 : 04:07:21

ISNUMERIC function works incorrectly. I added more contraints:

select model, type
from product
where
(ISNUMERIC(model) =1 and charindex('.',model)=0 and charindex(',',model)=0 and charindex('-',model)=0 and charindex('e',model)=0 and charindex('(',model)=0)
or
(model NOT LIKE '%[^a-z]%')

However there is still one char that I am lacking. What could it be?

yoyosh
Starting Member

27 Posts

Posted - 2013-01-07 : 04:07:59

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-07 : 04:14:39

See this..........

DECLARE @product TABLE( model varchar(100))
insert into @product
SELECT '2,34.4' union all
SELECT '23rw44' union all
SELECT '2344' union all
SELECT '2e344' union all
SELECT 'AFAF' union all
SELECT 'afbiafu241' union all
SELECT 'afbiafu'
SELECT * FROM @product
WHERE model NOT LIKE '%[^0-9]%' OR model NOT LIKE '%[^a-z]%'

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 10:41:37

quote:
Originally posted by yoyosh

ISNUMERIC function works incorrectly. I added more contraints:

select model, type
from product
where
(ISNUMERIC(model) =1 and charindex('.',model)=0 and charindex(',',model)=0 and charindex('-',model)=0 and charindex('e',model)=0 and charindex('(',model)=0)
or
(model NOT LIKE '%[^a-z]%')

However there is still one char that I am lacking. What could it be?

ISNUMERIC returns 1 for lots of other characters too that form part of numeric string

see

http://beyondrelational.com/modules/2/blogs/70/posts/10803/enhanced-isnumeric-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yoyosh
Starting Member

27 Posts

Posted - 2013-01-08 : 03:52:08

You are right. I figured out that dollar sign '$' was the problem.

By the way, why
model NOT LIKE '%[^0-9]%'
is different than
model LIKE '%[0-9]%'
?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 04:20:31

quote:
Originally posted by yoyosh

You are right. I figured out that dollar sign '$' was the problem.

By the way, why
model NOT LIKE '%[^0-9]%'
is different than
model LIKE '%[0-9]%'
?

in first case it filters provided there's at least 1 non numeric data

in latter case it returns all which has at least one numeric data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources