| Author |
 Topic  |
|
|
yoyosh
Starting Member
27 Posts |
 Posted - 01/07/2013 : 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 - 01/07/2013 : 03:46:13
|
| EDIT: I am getting more records than expected |
 |
|
|
yoyosh
Starting Member
27 Posts |
Posted - 01/07/2013 : 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 - 01/07/2013 : 04:07:59
|
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? |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 01/07/2013 : 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 |
Edited by - bandi on 01/07/2013 04:32:55 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 01/07/2013 : 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 - 01/08/2013 : 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
India
47069 Posts |
Posted - 01/08/2013 : 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/
|
|
|
| |
Topic |
|
LIKE problems
