SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 LIKE problems
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yoyosh
Starting Member

27 Posts

Posted - 01/07/2013 :  03:40:40  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
EDIT: I am getting more records than expected
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 01/07/2013 :  04:07:21  Show Profile  Reply with Quote
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?
Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 01/07/2013 :  04:07:59  Show Profile  Reply with Quote
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?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/07/2013 :  04:14:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2013 :  10:41:37  Show Profile  Reply with Quote
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/

Go to Top of Page

yoyosh
Starting Member

27 Posts

Posted - 01/08/2013 :  03:52:08  Show Profile  Reply with Quote
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]%'
?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/08/2013 :  04:20:31  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000