Comparation aritmetic with a nvarchar type field

Ramon Quintana

7y1.6k

Hi,

I want to compare numerics values with a varchar type field, so i have this table (Account_Master_Group2):

where fields (Inicio and Fin) are nvarchar type, so i run this query:

but i got records out of the range from 50000000 and 89999999:

My question is: How can i handle that nvarchar fields as a numeric fields? to avoid records out of the range.

Thanks WORLD

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar7y ago
Hi,

If you have your values like 'R1110110' then you can not convert to numeric because you have 'R' in that column. I look at your second error screen there you have extra closing ) and , after your where clause. Please reomve and try again.
0
Nitin Sontakke7y ago
For some reason, I just fail to see where exactly the isnumeric is being applied.

As stated in my very first post, will you please paste the query in text format as oppose to an image.

Please help me help you in a much better way.
0
Nitin Sontakke7y ago
This is because the conversion is applied each and every row in the table. As such if you have any rows where conversion cannot take place, for example, a string value, then you will get an error.

The work around is to include only those rows where value can successfully be converted to bigint.

you can add:

where isnumeric([column_name]) = 1

see https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-2017

Another point is, not that it matters, but you may now wish to get rid of the sigle quotes around the numbers now that the values are converted.
0
Ramon Quintana7y ago
Hi NITIN
where
convert(bigint, [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta) >= '50000000'
and
convert(bigint, [1RQ_BPCSQL1].dbo.Query_Base.Cuentas_Maestro_Numero_cuenta) <= '89999999'

I got error

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.

MR. KONETI and KUMAR

I try to use CAST but it didn´t work:

cast([1RQ_BPCSQL1].dbo.Account_Master_Group2.inicio AS INT)

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'S6687904' to data type int.
What more can i do??

Please and Thanks
0
Sivakumar Koneti7y ago
Hello Ramon Quintana

Cast the varchar column after then compare

look once this below link you get an idea

https://dba.stackexchange.com/questions/167489/detect-if-any-values-in-nvarchar-columns-are-actually-unicode
0
Nitin Sontakke7y ago
You will need to convert the nvarchar column to int or bigint on the fly.

If possible, i suggest you make the change permanent by modifying the data type of the column.

If you don't or cannot, please use convert function as

convert(bigint, [column_name]) <= 5000

or as the case may be.

Had you pasted your query in text format (as oppose to image) I would have rewritten it for you.
+1
Jignesh Kumar7y ago
Hi Ramon,

Please convert varchar value to decimal or int while comparing for example.
Declare @varchar varchar(50)
set @varchar = 50000000
select CAST(@varchar AS DECIMAL(18,2)) as decimalValue


set @varchar = 89999999
select CAST(@varchar AS int) as intValue
0