Data conversion error

[Nik1985]

Hi,

I have been trying to convert nvchar into int but I get this error message
"Conversion failed when converting the nvarchar value '
691' to data type int".
There is a meta character before the number 6. It shows as a small box when copied onto text. How do i eliminate this entry?
I want to convert all entries other than those which contains the small box character.

Can you please help me with this problem?

Below is the snapshot of this problem:

AAAAAAAAA BBBB CCCC
670003945 0 99 8033
670000086 3780 8033
670000088 2940 8033

In column BBBB, the first entry is not being converted to int. The error "Conversion failed when converting the nvarchar value '0 99' to data type int" is thrown.

Thanks.

 

[markros]

Most likely it's one of the characters such as TAB, CR or LF

If you want to find only entries having numbers and no "funky" characters, then

select * from myTable where BBBB not like '%^[0-9]'

PluralSight Learning Library

 

[Nik1985]

Hi!!!

I tried it but I am still getting the same error. Conversion failed when converting the nvarchar value '0 99' to data type int.

 

[markros]

What did you try exactly? The SELECT statement I've written - it could not have given you this error.

If not, post the statement you tried.

PluralSight Learning Library

 

[Nik1985]

select
(convert(int, BBBB)) as BBBB
from
Table Name
where
AAAAAAAAA like '67000%'
and CCCC = '8033'
and BBBB not like '%^[0-9]'

 

[Nik1985]

I need to select the rows other than the row containing 0 99.
I need this to find out the average of the column BBBB. I have just provided a small snapshot of the table in my initial post.

Thanks

 

[bborissov]

Why you not store the numbers in the number type column?

What is the problem to have numeric/(tiny/small)integer type column, where you could store these values?

You ask for problems with the design of the table and you get them.

Try this:

select (convert(int, BBBB)) as BBBB
from [Table Name]
where AAAAAAAAA like '67000%'
  and CCCC = '8033'
  and ISNUMERIC(BBBB+'.e0') = 1

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Nik1985]

@Bborissov: Thanks for the tip. This database was created a long time ago by someone else. I need to use this for my work.

Also, I have another question. What if the tab (small box character) is at the beginning of the field, as in instead of "0 99" if it was " 099". The ISNUMERIC(BBBB+'.e0') = 1 doesn't work.

for ex.

AAAAAAAAA BBBB CCCC
670006500 691 2221
670007600 233 2221

I get this error
"Conversion failed when converting the nvarchar value
' 691' to data type int."

The code that I used was

select
(convert(int, BBBB)) as BBBB
from
Table Name
where
AAAAAAAAA like '67000%'
and CCCC = '2221'
and BBBB not like '%^[0-9]'
and ISNUMERIC(BBBB+'.e0') = 1

 

[Nik1985]

Sorry, I pasted the wrong code.
The code that i used was

select
(convert(int, BBBB)) as BBBB
from
Table Name
where
AAAAAAAAA like '67000%'
and CCCC = '2221'
and ISNUMERIC(BBBB+'.e0') = 1

 

[bborissov]

select (convert(int, BBBB)) as BBBB
from [Table Name]
where AAAAAAAAA like '67000%'
  and CCCC = '8033'
  and ISNUMERIC('1'+BBBB+'.e0') = 1

And my suggestion is to redesign the table, whit 2-3 T-SQL commands you can get rid of the nvarchar field that keeps numeric values.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Try instead:

;with cte as (select  
BBBB
from 
Table Name
where 
AAAAAAAAA like '67000%'
and CCCC = '8033'
and BBBB not like '%^[0-9]') 

select convert(int, BBBB) as BBBB from cte

PluralSight Learning Library

 

[bborissov]

markros:
This:

and BBBB not like '%^[0-9]')

will filter nothing (from the example), just because there is at least one digit in the record.

DECLARE @Test TABLE (BBBB nvarchar(200))

INSERT INTO @Test VALUES (CHAR(9)+'691')
INSERT INTO @Test VALUES ('6'+CHAR(9)+'91')
INSERT INTO @Test VALUES ('691')


select *
from @Test 
WHERE BBBB not like '%^[0-9]'

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

I would encourage you to use the GetNumbers User Defined Function from my blog here:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server

The best thing to do would be to run it on your table to 'fix' your data first. And then add a trigger that prevents bad data from getting in to the database.

With the GetNumbers function, your code would look like this:

select  
convert(int, [!]dbo.GetNumbers([/!]BBBB[/!])[/!]) as BBBB
from
Table Name
where
AAAAAAAAA like '67000%'
and CCCC = '2221'

If there is data that does not contain any numbers, you will get a 0 returned, as you can see from this example:

DECLARE @Test TABLE (BBBB nvarchar(200))

INSERT INTO @Test VALUES (CHAR(9)+'691')
INSERT INTO @Test VALUES ('6'+CHAR(9)+'91')
INSERT INTO @Test VALUES ('691')
Insert Into @Test Values('')
Insert Into @Test Values('Hello')

select *, Convert(Int, dbo.GetNumbers(BBBB))
from @Test

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I made a typo in my query.

It should have been

DECLARE @Test TABLE (BBBB nvarchar(200))

INSERT INTO @Test VALUES (CHAR(9)+'691')
INSERT INTO @Test VALUES ('6'+CHAR(9)+'91')
INSERT INTO @Test VALUES ('691')
insert into @Test values ('aaa')

;with cte as (
select *
from @Test 
WHERE BBBB not like '%[^0-9]%')

select convert(int, BBBB) as BBB from cte

PluralSight Learning Library