October 27, 2004 at 6:15 pm
How can I add some
"Ignore alpha, report with alpha"
logic to the following when doing
"WHERE (ITEMNO < '99999')" since I still
want "10001C" to display in the results but
currently get a "Syntax error converting the
varchar value '10001C ' to a column of
data type int" error.
==============================================
SELECT CAST(ITEMNO AS INT) AS Item
FROM [ITEMMAST-10-25-04]
WHERE (ITEMNO < '99999')
==============================================
Sample ITEMMAST-10-25-04 Table
ITEMNO ("20" charater field/trailing spaces)
10001C
72001
8900001
==============================================
Final Results
10001C
10001
==============================================
Also, how can I eliminate 8900001 from displaying
in the results since a "WHERE (ITEMNO < '99999')"
fails to stop it and a substring
still makes it happen ?
October 28, 2004 at 2:25 am
I reckon you need to use the isnumeric() function and treat the numeric and non-numeric codes separately. Try something like this:
SELECT ITEMNO AS Item
FROM [ITEMMAST-10-25-04]
WHERE isnumeric(itemno) = 1 and cast(ITEMNO as int) < 99999
union
SELECT ITEMNO AS Item
FROM [ITEMMAST-10-25-04]
WHERE isnumeric(itemno) = 0 and ITEMNO < '99999'
As a refinement, you could probably replace the UNION with a CASE construction, which may improve performance, if that's an issue.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2004 at 2:31 am
Use
ISNUMERIC(ITEMNO) = 1
to test for numbers only
use
PATINDEX('%[A-Z]%',ITEMNO)
to determine the location of alpha
if the alpha is always at the end then
SELECT CAST(CASE WHEN ISNUMERIC(ITEMNO) = 1
THEN ITEMNO
ELSE LEFT(ITEMNO,LEN(ITEMNO)-1)
END AS INT) AS Item
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply