View failing on data conversion - SQL Server-CodePudding

I need help redesigning a view. Currently it is giving an error when you do a simple select of the view. The error is as follows.

Error converting data type nvarchar to bigint

This is the current design of the view.

SELECT  
    FirstName,
    MiddleName, 
    LastName,           
    CONVERT(bigint, SUBSTRING(Reference, 2, 10)) AS LogNum,     
    Address,
    Birthday,
    Gender,
    BirthDate,
    Gender,     
    ClientCode 
FROM 
    dbo.Client 
WHERE 
    (Reference LIKE 'C%')

The issue is happening because the reference column which has a nvcarchar(16) datatype has values that are as follows.

'C3456423445'  
'2234567310'  
'C8921244532'

The substring function basically strips out the "C" and returns the 10 digit numbers after it as a bigint.

What is happening now is there is new data that came into the client table that has values for reference column as follows

"CC4309842387"    
"CC29383761760"

Since there is an extra C in the new values, the function is not doing its job.

I need to redesign the view so that it can handle both iterations of the values in the reference column. What is essential is the 10 digit numbers coming out intact for the front end report.

Thank you for your help.

CodePudding user response：

If there are only two options "C" and "CC", then you can use REPLACE with LEFT instead of SUBSTRING.

SELECT  
    FirstName,
    MiddleName, 
    LastName,           
    Convert(bigint,Left(Replace(Reference, 'C',''),10)) AS LogNum,     
    Address,
    Birthday,
    Gender,
    BirthDate,
    Gender,     
    ClientCode 
FROM dbo.Client 
WHERE (Reference LIKE 'C%')

CodePudding user response：

If other than preceding C's, you can use patindex() to find the position of the 1st numeric

Also, I prefer try_convert() because if the conversion fails, it will return a NULL rather than throwing an error.

Example

Declare @YourTable Table ([Reference] nvarchar(50))  Insert Into @YourTable Values 
 ('C3456423445')
,('2234567310')
,('C8921244532')
,('CC29383761760')
,('XX29383761760')

Select *
      ,NewValue = try_convert(bigint,substring([Reference],patindex('%[0-9]%',[Reference]),25))
 from @YourTable

Results

Reference       NewValue
C3456423445     3456423445
2234567310      2234567310
C8921244532     8921244532
CC29383761760   29383761760
XX29383761760   29383761760