?
Solved

SQL Syntax using ISNUMERIC and between

Posted on 2009-05-18
31
Medium Priority
?
497 Views
Last Modified: 2012-08-13
Trying to pull all rows that match my criteria but am struggling since FOLIO is a varchar that may have text, but I'm filtering on a number range.

How can I tweak this sql to work properly?

I understand ISNUMERIC returns boolean, but I just can't figure out how to filter out the non-numeric values in FOLIO.
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) BETWEEN 363 and 414

Open in new window

0
Comment
Question by:RankenIS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 11
  • 5
  • +1
31 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415658

SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 and Folio BETWEEN 363 and 414
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415663
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 AND Folio BETWEEN 363 and 414
0
 

Author Comment

by:RankenIS
ID: 24415756
I received the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ARCHECK' to data type int.
I think this is due to sql server processing the second half of the AND before the first half.  Could I dump this into a case statement or an additional where by only the ISNUMERIC(FOLIO)=1 get processed by the BETWEEN?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24415767
select * from (
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 ) a where Folio BETWEEN 363 and 414
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415775

SELECT * FROM (
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1
) A
WHERE Folio BETWEEN 363 and 414
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415798
:)
0
 

Author Comment

by:RankenIS
ID: 24415818
Another Convert Error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SB' to data type int.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415838
you didn't get that error from running this statement.

select * from (
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 ) a where Folio BETWEEN 363 and 414
0
 

Author Comment

by:RankenIS
ID: 24415886
Yes I did.  In the Transactions table, FOLIO is a type char(8)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415923
the exact query above produces that error?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415948
select * from (
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 ) a
where Folio BETWEEN '363' and '414'  
0
 

Author Comment

by:RankenIS
ID: 24415952
see screencap
Untitled.jpg
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415965
yep, im going through it right now...trying to figure out a good way to do it.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415983
did u try putting quotes around 363 and 414 , check my last post ;
0
 

Author Comment

by:RankenIS
ID: 24415988
The quotes worked.  However, I'm grabbing FOLIO values outside the range, such as 37, 40000, 3895, etc.  because it's doing a text compare, maybe?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24416004
one way to do it:

select newfield =  CAST(fld1 as int), *
into #temp
from #r
where ISNUMERIC(fld1) = 1

select * from #temp
where newfield between 1 and 2

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24416010
sorry

select newfield =  CAST(fld1 as int), *
into #temp
from #r
where ISNUMERIC(fld1) = 1

select * from #temp
where newfield between 363 and 414
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24416034
select * from
(
SELECT * FROM TRANSACTIONS
WHERE FOLIO NOT LIKE '%[a-z]%'
and ISNUMERIC(folio) =1
) a
where
Folio BETWEEN 363 and 414
0
 

Author Comment

by:RankenIS
ID: 24416084
select * from
(
SELECT * FROM TRANSACTIONS
WHERE FOLIO NOT LIKE '%[a-z]%'
and ISNUMERIC(folio) =1
) a
where
Folio BETWEEN 363 and 414
gives

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SB' to data type int.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24417851
This is getting painful, surely just a simple query as follows would do:

Select      *
From      (
            SELECT *
            FROM TRANSACTIONS
            WHERE FOLIO LIKE '[0-9][0-9][0-9]'             -- Modify as appropriate
            ) a
Where      CAST(Folio as integer) BETWEEN 363 and 414

If not post the structure of your table TRANSACTIONS.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24417931
agreed...made me want to cry a little bit.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24417987
:)
0
 

Author Comment

by:RankenIS
ID: 24421243
Select *
From (
SELECT *
FROM TRANS_HIST
WHERE FOLIO LIKE '[0-9][0-9][0-9]'
) a
Where CAST(Folio as integer) BETWEEN 363 and 414

Returns the same error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SB' to data type int.

Table design shown below:

/****** Object:  Table [dbo].[TRANS_HIST]    Script Date: 05/19/2009 07:40:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TRANS_HIST](
	[SOURCE_CDE] [char](2) NOT NULL,
	[GROUP_NUM] [int] NOT NULL,
	[TRANS_KEY_LINE_NUM] [int] NOT NULL,
	[TRANS_DTE] [datetime] NULL,
	[TRANS_AMT] [numeric](11, 2) NOT NULL DEFAULT (0),
	[TRANS_DESC] [char](40) NULL,
	[FOLIO] [char](8) NULL,
	[ACCT_CDE] [char](30) NULL,
	[PROJECT_CODE] [char](10) NULL,
	[ENCUMB_GL_FLAG] [char](1) NOT NULL,
	[ENCUMB_GL_TRANS_ST] [char](1) NOT NULL,
	[AP_SBS_ID_NUM] [int] NULL,
	[AP_SBS_CDE_SUBSID] [char](2) NULL,
	[INVOICE_NUM] [char](18) NULL,
	[ID_NUM] [int] NULL,
	[SUBSID_CDE] [char](2) NULL,
	[OFFSET_FLAG] [char](1) NULL,
	[SUBSID_TRANS_STS] [char](1) NOT NULL,
	[PAYABLE_CHECK_DTE] [datetime] NULL,
	[CHECK_NUM_ALPHA] [char](2) NULL,
	[CHECK_NUM_NUM] [int] NULL DEFAULT (0),
	[TRANS_PO_NUM_GRP_N] [int] NULL,
	[PO_LINE_NUM] [int] NULL,
	[DISCOUNT] [numeric](11, 2) NOT NULL DEFAULT (0),
	[RECEIPT_NUM] [char](9) NULL,
	[ABA_NUM] [char](8) NULL,
	[AR_CDE] [char](3) NULL,
	[GL_SEL_FLAG] [char](1) NULL,
	[PARTIAL_ORDER_PO] [char](1) NULL,
	[DISCOUNT_TAKEN] [char](1) NULL,
	[THIRD_PTY_BILL_ID] [int] NULL,
	[THIRD_PTY_BILL_AMT] [numeric](11, 2) NOT NULL DEFAULT (0),
	[OPEN_ITEMS_INV_NUM] [char](10) NULL,
	[CHG_FEE_CDE] [char](5) NULL,
	[FEES_SEQUENCE_NUM] [int] NULL,
	[OI_SOURCE_CDE] [char](2) NULL,
	[OI_GROUP_NUM] [int] NULL,
	[OI_TRANS_KEY_LINE_NUM] [int] NULL,
	[OI_ALLOCATION] [char](1) NOT NULL DEFAULT ('N'),
	[FAP_CDE] [char](10) NULL,
	[FUND_CDE] [int] NULL,
	[UNDEFINED_DTE_FLD] [datetime] NULL,
	[UNDEFINED_1A_FLD_1] [char](1) NULL,
	[UNDEFINED_1A_FLD_2] [char](1) NULL,
	[UNDEFINED_10A_FLD] [char](10) NULL,
	[UNDEFINED_11_2_FLD] [numeric](11, 2) NOT NULL DEFAULT (0),
	[WORK_PERCENT] [numeric](5, 2) NOT NULL DEFAULT (0),
	[TAX_REFUND_CDE] [char](5) NULL,
	[TAX_REFUND_AMT] [numeric](11, 2) NOT NULL DEFAULT (0),
	[FOR_CUR_TRANS_AMT] [numeric](11, 2) NOT NULL DEFAULT (0),
	[FOR_CUR_DISCOUNT] [numeric](11, 2) NOT NULL DEFAULT (0),
	[CURR_CODE] [char](3) NULL,
	[EXCHANGE_RATE] [numeric](14, 7) NOT NULL DEFAULT (0),
	[PAYMENT_PLAN_CDE] [char](2) NULL,
	[PAY_PLAN_SEQ_NUM] [int] NULL,
	[AR_INVOICE_NUM] [char](9) NULL,
	[ELIG_1098T] [char](2) NULL,
	[CREDIT_MEMO] [char](18) NULL,
	[ASSET_NUMBER] [char](15) NULL,
	[CHG_TRM_TRAN_HIST] [char](2) NULL,
	[CHG_YR_TRAN_HIST] [char](5) NULL,
	[GOVT_FORM_CODE] [char](15) NULL,
	[GOVT_LABEL_CODE] [char](10) NULL,
	[PAYMENT_PLAN_NUM] [int] NULL,
	[USER_NAME] [char](15) NULL,
	[JOB_NAME] [char](30) NULL,
	[JOB_TIME] [datetime] NULL,
 CONSTRAINT [PK_TRANS_HIST] PRIMARY KEY CLUSTERED 
(
	[SOURCE_CDE] ASC,
	[GROUP_NUM] ASC,
	[TRANS_KEY_LINE_NUM] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [AP_REF_298826] FOREIGN KEY([GOVT_FORM_CODE], [GOVT_LABEL_CODE])
REFERENCES [dbo].[EX_GOVT_FORM_SELECT_LABELS] ([GOVT_FORM_CODE], [GOVT_LABEL_CODE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [AP_REF_298826]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_11778] FOREIGN KEY([SUBSID_CDE])
REFERENCES [dbo].[SUBSID_DEF] ([SUBSID_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_11778]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_11781] FOREIGN KEY([AP_SBS_CDE_SUBSID])
REFERENCES [dbo].[SUBSID_DEF] ([SUBSID_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_11781]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_125851] FOREIGN KEY([TAX_REFUND_CDE])
REFERENCES [dbo].[TAX_REFUND] ([TAX_REFUND_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_125851]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_137587] FOREIGN KEY([CURR_CODE])
REFERENCES [dbo].[CURRENCY_MASTER] ([CURR_CODE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_137587]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_226900] FOREIGN KEY([ASSET_NUMBER])
REFERENCES [dbo].[ASSET_MASTER] ([ASSET_NUMBER])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_226900]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_238733] FOREIGN KEY([OI_SOURCE_CDE], [OI_GROUP_NUM], [OI_TRANS_KEY_LINE_NUM])
REFERENCES [dbo].[TRANS_HIST] ([SOURCE_CDE], [GROUP_NUM], [TRANS_KEY_LINE_NUM])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_238733]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_255233] FOREIGN KEY([FAP_CDE])
REFERENCES [dbo].[PF_FAP_DEF] ([FAP_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_255233]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_255238] FOREIGN KEY([FUND_CDE])
REFERENCES [dbo].[PF_FUND_CDE_MSTR] ([FUND_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_255238]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_45808] FOREIGN KEY([ID_NUM])
REFERENCES [dbo].[NAME_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_45808]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_45811] FOREIGN KEY([AP_SBS_ID_NUM])
REFERENCES [dbo].[NAME_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_45811]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_45946] FOREIGN KEY([AP_SBS_ID_NUM], [INVOICE_NUM])
REFERENCES [dbo].[INVOICE_HEADER] ([ID_NUM], [INVOICE_NUM])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_45946]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_5920] FOREIGN KEY([ACCT_CDE])
REFERENCES [dbo].[GL_MASTER] ([ACCT_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_5920]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_5946] FOREIGN KEY([PROJECT_CODE])
REFERENCES [dbo].[PROJECT_DEF] ([PROJECT_CODE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_5946]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_72639] FOREIGN KEY([AR_CDE])
REFERENCES [dbo].[AR_CODE] ([AR_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_72639]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_72642] FOREIGN KEY([RECEIPT_NUM])
REFERENCES [dbo].[RECEIPT_HEADER] ([RECEIPT_NUM])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_72642]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_72645] FOREIGN KEY([THIRD_PTY_BILL_ID])
REFERENCES [dbo].[NAME_MASTER] ([ID_NUM])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_72645]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [BU_REF_7637] FOREIGN KEY([SOURCE_CDE])
REFERENCES [dbo].[SOURCE_MASTER] ([SOURCE_CDE])
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [BU_REF_7637]
GO
ALTER TABLE [dbo].[TRANS_HIST]  WITH CHECK ADD  CONSTRAINT [CKC_USER_NAME_TRANS_HIST] CHECK  ((len([USER_NAME])<(16)))
GO
ALTER TABLE [dbo].[TRANS_HIST] CHECK CONSTRAINT [CKC_USER_NAME_TRANS_HIST]

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24421270
so you were against my temp table idea?
0
 

Author Comment

by:RankenIS
ID: 24421330
Temp table solution led to same issue:

select newfield = CAST(folio as int), *
into #temp
from trans_hist
where ISNUMERIC(folio) = 1
resulted in error:
 
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '.' to data type int.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24421393
couldn't have..I tested it and it worked fine...you have to make sure you select fromthe temp table afterwards.

select newfield =  CAST(folio as int), *
into #temp
from trans_hist
where ISNUMERIC(folio) = 1

select * from #temp
where newfield between 363 and 414
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24422654
>>Returns the same error:<<
I suspect you overlooked my comment: "Modify as appropriate"

So that we do not have to go around and around with this problem, can you put us out of our collective misery and post the data that is failing.  Even better would be if you could attach the table.
0
 

Author Comment

by:RankenIS
ID: 24424779
I have atttempted every query that has been posted here.  All of them come back with a conversion failure for several different varchar values.
I know it should be working with most of these queries, but for some reason the cast or convert doesn't continue on failure.
I am not going to attach the table since it 200k plus rows with CPNI data.
I attached the error received below its respective query.
Thank you for your efforts.  I'm going to contact the vendor later today and see if they have any insight.
 

select * from 
(
SELECT * FROM TRANS_HIST
WHERE FOLIO NOT LIKE '%[a-z]%' 
and ISNUMERIC(folio) =1 
) a 
where 
Folio BETWEEN 363 and 414
 
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'MASTERCA' to data type int.
 
Select      * 
From      (
            SELECT *
            FROM TRANS_HIST
            WHERE FOLIO LIKE '[3-4][0-9][0-9]'       
            ) a 
Where      CAST(Folio as integer) BETWEEN 363 and 414
 
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ARCHECK' to data type int.
 
 
drop table #temp
select newfield =  CAST(folio as int), *
into #temp 
from trans_hist
where ISNUMERIC(folio) = 1
 
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '.' to data type int.

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24427583
>>WHERE FOLIO LIKE '[3-4][0-9][0-9]' <<
[3-4] Interesting, did someone suggest this?

I know I am beating a dead horse, but did you try do ""Modify as appropriate" the WHERE clause to match the table structure which was unknown at the time?

As in:

Select      *
From      (
            SELECT *
            FROM TRANS_HIST
            WHERE FOLIO LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'      
            ) a
Where      CAST(Folio as bigint) BETWEEN 363 and 414

0
 

Author Comment

by:RankenIS
ID: 24431459
I modified the line
WHERE FOLIO LIKE '[3-4][0-9][0-9]
because FOLIO is defined as a char(8) in the table and has text or numerical values.
I was trying to filter values between 363 and 414, but the between function wasn't working.  
it continues to break once it finds a non numerical value.
I'm most likely going to give up searching for FOLIO and use 50+ like statements in ca case structure against the field TRANS_DESC since this seems like a giant pain for everyone.
0
 

Accepted Solution

by:
RankenIS earned 0 total points
ID: 24527240
Wound up using a enumerated case statement.
Thanks for the help.

select Category=
case
when folio in ('364','365','367','368','369','371','372','376','381','382','383','384','385','386','387','388','390','393','397','400','401','402','405','406','407','408','409','410','411','412','413')
then trans_desc
else 'Other'
end

Open in new window

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

764 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