0

I am using SQL Server Management Studio and have a table defined with S_id AS NVARCHAR (80), and this cannot be changed. Now there are some values coming as AEC123, ID-33009, 456KP889 which according to me are causing an error in my following query

CONVERT(bigint, S_id)

and I get an error

Error converting data type nvarchar to bigint

How do I fix this? If there are values of S_id such as described above should be excluded from my SELECT statement, how do I achieve this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sca
  • 175
  • 1
  • 13
  • 2
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Aug 16 '23 at 15:55
  • 1
    You can test if the value is BIGINT using a strategy like what's discussed in the question link posted above. – devlin carnate Aug 16 '23 at 15:59
  • 6
    Use `TRY_CONVERT` instead. Though why are you converting the value to a `bigint` in the first place when the values clearly *aren't* a numerical value. This smells like an [XY problem](xyproblem.info) to me. – Thom A Aug 16 '23 at 15:59
  • 1
    @ThomA there are some numerical values too such as 2193,44567689, etc.. – Sca Aug 16 '23 at 16:07
  • 1
    How do I use TRY_CONVERT ? I am not able to get the syntax @ThomA – Sca Aug 16 '23 at 16:09
  • 1
    The syntax is identical to `CONVERT`'s, @Sca . `TRY_CONVERT(, {, – Thom A Aug 16 '23 at 16:13
  • 1
    Got it but still throws an error : Error converting data type nvarchar to bigint. – Sca Aug 16 '23 at 16:14
  • 4
    `TRY_CONVERT` *cannot* throw that error. This means you're trying to change the datatype to an `bigint` somewhere else. – Thom A Aug 16 '23 at 16:14
  • @ThomA it did work, thank you! although I read that if this function cannot convert, it will return a NULL and currently this field would not accept a NULL according to my table definition. – Sca Aug 16 '23 at 16:38
  • Unless you are inserting into the table from that function it shouldn't matter. – Charlieface Aug 16 '23 at 17:09

0 Answers0