[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

SQL Syntax using ISNUMERIC and between

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
RankenIS
Asked:
RankenIS
  • 11
  • 11
  • 5
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:

SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 and Folio BETWEEN 363 and 414
0
 
chapmandewCommented:
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 AND Folio BETWEEN 363 and 414
0
 
RankenISAuthor Commented:
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
Independent Software Vendors: 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!

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

SELECT * FROM (
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1
) A
WHERE Folio BETWEEN 363 and 414
0
 
chapmandewCommented:
:)
0
 
RankenISAuthor Commented:
Another Convert Error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SB' to data type int.
0
 
chapmandewCommented:
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
 
RankenISAuthor Commented:
Yes I did.  In the Transactions table, FOLIO is a type char(8)
0
 
chapmandewCommented:
the exact query above produces that error?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select * from (
SELECT * FROM TRANSACTIONS
WHERE ISNUMERIC(FOLIO) = 1 ) a
where Folio BETWEEN '363' and '414'  
0
 
RankenISAuthor Commented:
see screencap
Untitled.jpg
0
 
chapmandewCommented:
yep, im going through it right now...trying to figure out a good way to do it.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
did u try putting quotes around 363 and 414 , check my last post ;
0
 
RankenISAuthor Commented:
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
 
chapmandewCommented:
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
 
chapmandewCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
select * from
(
SELECT * FROM TRANSACTIONS
WHERE FOLIO NOT LIKE '%[a-z]%'
and ISNUMERIC(folio) =1
) a
where
Folio BETWEEN 363 and 414
0
 
RankenISAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
chapmandewCommented:
agreed...made me want to cry a little bit.
0
 
Anthony PerkinsCommented:
:)
0
 
RankenISAuthor Commented:
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
 
chapmandewCommented:
so you were against my temp table idea?
0
 
RankenISAuthor Commented:
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
 
chapmandewCommented:
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
 
Anthony PerkinsCommented:
>>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
 
RankenISAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
RankenISAuthor Commented:
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
 
RankenISAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 11
  • 11
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now