Conversion failed when converting the nvarchar...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion failed when converting the nvarchar...

Author

Topic

Leeoniya
Starting Member

8 Posts

Posted - 2008-03-11 : 20:17:25

One of my clients decided to put letters into their customers' account numbers. They have a numbering scheme where all temporary accounts have a letter in the account OR are numbered greater than 33000, and all permanent accounts are all digits and less than or equal to 33000. all primary accounts have a NumberSuffix of 000.

Now i am tasked with retrieving all primary, non-temp accounts. I cannot simply do WHERE Number <= 33000 because when it gets to an account containing a letter like "00A01", it craps out and says "Conversion failed when converting the nvarchar value '00A01' to data type int."

So decided to run a filtering query first to filter out all accounts with letters, and then from that dataset select all accounts <=33000.

WITH members (FirstName, LastName, Number, NumberSuffix) AS
(
SELECT 
  dbo.Entity.FirstName,
  dbo.Entity.LastName,
  dbo.Entity.Number,
  dbo.Entity.NumberSuffix
FROM
  dbo.Entity
WHERE
  NumberSuffix = 000 AND
  Number NOT LIKE '%A%' AND
  Number NOT LIKE '%B%' AND
  Number NOT LIKE '%C%' AND
  Number NOT LIKE '%D%' AND
  Number NOT LIKE '%E%' AND
  Number NOT LIKE '%F%' AND
  Number NOT LIKE '%G%' AND
  Number NOT LIKE '%H%' AND
  Number NOT LIKE '%I%' AND
  Number NOT LIKE '%J%'
)

SELECT *
FROM
  members
WHERE
  Number <= 33000
ORDER BY
  Number

when i do this, i get the same error for some reason. Yet when i execute this at the end instead:

SELECT *
FROM
  members
WHERE
  Number LIKE '%A%'
ORDER BY
  Number

i get an empty set (meaning it actually does get filtered).
but somehow it still able to participate in a range comparison?

WHY??

thanks,
Leon

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-11 : 21:32:31

Because SQL is declarative. Just because you write a query in one way does not mean that is how it is executed. Try a case statement:
SELECT
dbo.Entity.FirstName,
dbo.Entity.LastName,
dbo.Entity.Number,
dbo.Entity.NumberSuffix
FROM
dbo.Entity
WHERE
(case when number not like '%[A-Z]%' 33001 else number end)<=33000

Alternatively you might get away with string compares by using the string '33000' instead of the number.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 21:36:11

Where number = '33000' --would return

the engine internally will try to convert the "number" column (which isn't a numeric data type) to numbers, since it is stored as text. it would of course error when trying to convert any non-numeric values to a number.

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 21:37:55

quote:
[i]
(case when number not like '%[A-Z]%' 33001 else number end)<=33000

this would be a little intensive..

quote:

Alternatively you might get away with string compares by using the string '33000' instead of the number.

now you are thinkin...it isn;t a number field after all, so criteria being a string makes the most sense.

Poor planning on your part does not constitute an emergency on my part.

Leeoniya
Starting Member

8 Posts

Posted - 2008-03-11 : 22:40:34

so if i understand correctly, i need to tell it to convert the Number field to an integer tell it to not to include records where the conversion fails/errors?

example please?

thanks.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-12 : 21:32:41

No you should not be converting it into an integer. Your number is not a number it is a string and needs to be treated as such. I am not sure what is wrong with my 2 suggestions, although having thought about it, your best bet might be

WHERE NUMBER BETWEEN '10000' and '99999'

or something like that. It would depend on your data and I don't have that detail.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-12 : 21:41:15

quote:
Originally posted by Leeoniya

so if i understand correctly, i need to tell it to convert the Number field to an integer tell it to not to include records where the conversion fails/errors?

example please?

thanks.

No, you change your condition to be comparing a string field (which it is) to a string instead of string < number

Lost and I both provided alternatives that said essentially the same thing...

Poor planning on your part does not constitute an emergency on my part.

Leeoniya
Starting Member

8 Posts

Posted - 2008-03-12 : 21:55:44

thanks, i'll test it out tomorrow :).

would it make sense to create a view with the letter'd numbers filtered out?

would it be possible to then convert that column in the view to numeric and index it?

Artoo
Starting Member

16 Posts

Posted - 2008-03-13 : 05:05:59

Reading your question you say "all primary accounts have a NumberSuffix of 000". Surely then you can:
SELECT * FROM dbo.Entity WHERE NumberSuffix = '000'

Alternatively

DECLARE @TempTable TABLE (
FirstName varchar(?),
LastName varchar(?),
Number int,
NumberSuffix varchar(?))

INSERT INTO @TempTable
SELECT
dbo.Entity.FirstName,
dbo.Entity.LastName,
dbo.Entity.Number,
dbo.Entity.NumberSuffix
FROM
dbo.Entity
WHERE
ISNUMERIC(Number) = 1

SELECT * FROM @TempTable
WHERE Number < 33000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 06:24:49

Isnumeric is not reliable

Select data from
(
select '345' as data union all
select '1,234' union all
select '15d1'
) as t
where isnumeric(data)=1

Madhivanan

Failing to plan is Planning to fail

Leeoniya
Starting Member

8 Posts

Posted - 2008-03-13 : 16:14:44

@Artoo you're confusing "primary account" with "permanent account". the NumberSuffix is just an indication of whether the contact is primary, spouse, child...etc. I was just trying to clarify why NumberSuffix was in my statement to avoid confusion.

the permanent status is determined by the "number" column only according to the scheme.

I think i'm sticking with this:

SELECT 
  dbo.Entity.FirstName,
  dbo.Entity.LastName,
  dbo.Entity.Number,
  dbo.Entity.NumberSuffix
FROM
  dbo.Entity
WHERE
  NumberSuffix = 000 AND 
  Number BETWEEN '00000' AND '33001' AND 
  Number NOT LIKE '%[A-Z]%'
ORDER BY
  Number

thanks all for your help,
Leon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources