?
Solved

How to use isnumeric() in MSSQL 2005?

Posted on 2010-01-07
12
Medium Priority
?
502 Views
Last Modified: 2012-08-13
Case 1:

select [rowselector]=0, [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(Money,t.amount),1), [countervalue], [clientreference]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0
and
(
('%153750%'='XX' or t.clientreference like N'%153750%') or ('%153750%'='XX' or r.type like N'%153750%') or
('%153750%'='XX' or c.nickname like N'%153750%') or ('%153750%'='XX' or t.currency like N'%153750%') or
('%153750%'='XX' or
(t.amount = case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then
convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end)
)
or
('%153750%'='XX' or t.remarks like N'%153750%') or ('%153750%'='XX' or t.remarks1 like N'%153750%') or
('%153750%'='XX' or t.remarks6 like N'%153750%') or ('%153750%'='XX' or t.remarks7 like N'%153750%')
) order by [id] asc

return nothing

Case 2:

select * from alltransaction where amount=convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))

can return the record of row


How should I edit the Case 1 query in order to display the same row of record of Case 2?

0
Comment
Question by:techques
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 26201766
can you please make your question simple...
nobody will understand anything from this query, full of %'12345%XX...
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 400 total points
ID: 26201885
you dont need to repeat "'%153750%'='XX' or " all is added with OR statements...

('%153750%'='XX' or t.clientreference like N'%153750%') or ('%153750%'='XX' or r.type like N'%153750%') or
('%153750%'='XX' or c.nickname like N'%153750%') or ('%153750%'='XX' or t.currency like N'%153750%') or
('%153750%'='XX' or
(t.amount = case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then
convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end)
)
or
('%153750%'='XX' or t.remarks like N'%153750%') or ('%153750%'='XX' or t.remarks1 like N'%153750%') or
('%153750%'='XX' or t.remarks6 like N'%153750%') or ('%153750%'='XX' or t.remarks7 like N'%153750%')

==>

('%153750%'='XX')
or (t.clientreference like N'%153750%')
or (r.type like N'%153750%')
or (c.nickname like N'%153750%')
or (t.currency like N'%153750%')
or (t.amount = (case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then
convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end))
or (t.remarks like N'%153750%')
or (t.remarks1 like N'%153750%')
or (t.remarks6 like N'%153750%')
or (t.remarks7 like N'%153750%')
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 400 total points
ID: 26201910
and even better

==>

('%153750%'='XX')
or (t.clientreference +' '+ r.type like +' '+ c.nickname +' '+ t.currency +' '+ t.remarks +' '+ t.remarks1 +' '+ t.remarks6 +' '+ t.remarks7 like N'%153750%')
or (t.amount = (case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then
convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:techques
ID: 26201912
select [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(Money,t.amount),1), [countervalue], [clientreference]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency
inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id
inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0
and

('%153750%'='XX' or t.amount = case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then
convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end)

order by [id] asc


Please just concentrate on
('%153750%'='XX' or t.amount = case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then
convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end)

It cannot return record, I checked that other lines are ok.
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 400 total points
ID: 26201922
So you mean if '%153750%' which I assume comes from a variable is a number then you want to filter the amount column by that number otherwise ignore that filter correct? If so you can do it like this: (I've replaced the "0" with t.amount in your query)
...
t.amount = case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then 
convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else t.amount end)
....

Open in new window

0
 

Author Comment

by:techques
ID: 26201960
replace t.amount with 0 did not help, it still return nothing.
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 400 total points
ID: 26202008
it's the other way around. replace 0 with t.amount. Please refer to my comment above.
Also t.amount datatype is decimal  correct? If so can you use the convert with the same width and decimals?
Finally is '%153750%' the correct number you're looking for? or is it 1537.50?

... 
t.amount = case when isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))=1 then  
convert(decimal(18,2), Substring('%153750%', 2, LEN('%153750%')-2)) else t.amount end) 
....

Open in new window

0
 

Author Comment

by:techques
ID: 26202060
finally is 153750

but the textbox can input 0.5567, but just strip off the 2 decimal places

or, user can input 153750.00

t.amount datatype is decimal correct - yes
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 26203352
Just remember that ISNUMERIC() does not report if all the characters are digits, but simply if the character value can be considered a float.  For example the following will report true (1):
ISNUMERIC('123456E12')
0
 

Author Comment

by:techques
ID: 26205783
if cannot use isnumeric()

so, how can i fix it in this case?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26207099
I don't think isnumeric is the problem. Can you please provide a better example of what are you looking for?
0
 

Author Comment

by:techques
ID: 26209223
I found the problem not caused by isnumeric()

but t inner join client c on t.clientid = c.id as there is no such id, so it cannot display the result.

Thanks for all help

I add more points
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question