Comparation aritmetic with a nvarchar type field

Question

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

Nitin Sontakke · Answer

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]) 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.

Jignesh Kumar · Answer

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.

Nitin Sontakke · Answer

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.

Nitin Sontakke · Answer

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.

Ramon Quintana · Answer

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) 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

Sivakumar Koneti · Answer

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

Jignesh Kumar · Answer

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

Comparation aritmetic with a nvarchar type field

Answers (7)