April 7, 2007 at 10:23 am
I have a table in a database that contains a nvarchar string in a column representing a military id. In many cases the string will be numeric (serial number of the soldier), but in other cases it may be non-numeric (for officers) or may contain non-integer characters (/ typically) for certain branches of service.
It is beneficial for users of the database to be able to do range checks for the numeric entries (since most are numeric) so I added code that looked like the following:
SELECT * from qSoldier
where qSoldier.regNo is not null
and len(qSoldier.regNo) > 0
and charindex(',',qSoldier.regNo) = 0
and charindex('/',qSoldier.regNo) = 0
and charindex(';',qSoldier.regNo) = 0
and charindex('.',qSoldier.regNo) = 0
and isNumeric(regNo) = 1
and qSoldier.regNo between RANGE1 and RANGE2
Where the ranges were passed in from a form.
This worked fine while my ISP was using SQL Server 2000. The ISP recently updated the database to SQL Server 2005 and this SQL no longer works and returns a message:
Conversion failed when converting the nvarchar value '10/238' to data type int.
I have tried various permutations such as creating a view that has everything other than the range to try and remove the illicit characters from the select. I've also tried an 'isReallyNumeric' function, but the results are the same.
Any help in resolving this would be much appreciated
Thanks
marc
PS I don't claim to be anything other than an amateur, so please forgive the likely inefficient code.
April 7, 2007 at 2:23 pm
SELECT
*
FROM qSoldier
WHERE regNo NOT LIKE '%[^0-9]%'
AND regNo BETWEEN RANGE1 AND RANGE2
Please make sure Range1 is less (lower) than Range2!
N 56°04'39.16"
E 12°55'05.25"
April 8, 2007 at 8:19 am
Thank's, Peter. At first I thought how simple a solution that was. Unfortunately it still gives an error:
Conversion failed when converting the nvarchar value 'NA' to data type int.
'NA' being on e of the character values in the field. It is almost as if the optimizer is reading the whole table before the where clause kicks in.
Any other suggestions?
marc
April 8, 2007 at 9:20 am
How can this error occur?
I don't do any type of conversion here!
The only reason for this, is that you don't post the full query. You must use this and put some other columns to it. Also, what is qSoldier? A view (or query)?
Please post FULL code so we can help you...
N 56°04'39.16"
E 12°55'05.25"
April 8, 2007 at 9:52 am
I agree that it does not make sense ...
qSoldier is a view, but the same problem occurs off of the base table. The full query I am used (off of the base table to avoid any other concerns) is:
SELECT regNo
FROM soldiers
WHERE regNo NOT LIKE '%[^0-9]%'
AND regNo BETWEEN 50000 AND 60000
The error reported back is:
Error -2147217913
Conversion failed when converting the nvarchar value '10/238' to data type int.
SELECT regNo
FROM soldiers
WHERE regNo NOT LIKE '%[^0-9]%'
AND regNo BETWEEN 50000 AND 60000
marc
April 8, 2007 at 9:58 am
Although it seems to work of I change the BETWEEN to be:
BETWEEN '50000' AND '60000'
So I guess it was the BETWEEN that was doing the conversion and ignoring the restrictions of the previous clause in the Where.
marc
April 8, 2007 at 10:00 am
Ahhh! You put numeric data in the BETWEEN? Then SQL Server tries to convert the base data to the datatype in the BETWEENS.
I use Developer Edition of SQL Server 2005 (with service pack 2a) and this test code
DECLARE
@Sample TABLE (Data VARCHAR(500))
INSERT
@Sample
SELECT '123:21311' UNION ALL
SELECT '123;21-311' UNION ALL
SELECT '12321,311' UNION ALL
SELECT '10/238' UNION ALL
SELECT '10238' UNION ALL
SELECT '23984234m234324'
SELECT
Data
FROM @Sample
WHERE Data NOT LIKE '%[^0-9]%'
gives me one record back:
10238
SELECT data from ( select Data
FROM @Sample
WHERE Data NOT LIKE '%[^0-9]%'
) as x where data between 5000 and 60000
N 56°04'39.16"
E 12°55'05.25"
April 8, 2007 at 12:33 pm
Thank's Peter,
marc
April 8, 2007 at 1:14 pm
You're welcome! Good luck.
N 56°04'39.16"
E 12°55'05.25"
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply