Solved

SQL Syntax using ISNUMERIC and between

Posted on 2009-05-18
31
480 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now