[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Syntax using ISNUMERIC and between

Posted on 2009-05-18
31
Medium Priority
?
502 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.​
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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