CODEWITHSUNDEEP
sqlsql-servert-sql
Matt Syvertson
Matt Syvertson

Reputation: 25

Error checking in T-SQL Script

I am self taught in T-SQL, so I am sure that I can gain efficiency in my code writing, so any pointers are welcomed, even if unrelated to this specific problem.

I am having a problem during a nightly routine I wrote. The database program that is creating the initial data is out of my control and is loosely written, so I have bad data that can blow up my script from time to time. I am looking for assistance in adding error checking into my script so I lose one record instead of the whole thing blowing up.

The code looks like this:

    SELECT convert(bigint,(SUBSTRING(pin, 1, 2)+ SUBSTRING(pin, 3, 4)+ SUBSTRING(pin, 7, 5) + SUBSTRING(pin, 13, 3))) AS PARCEL, taxyear, subdivisn, township, propclass, paddress1, paddress2, pcity

    INTO [ASSESS].[dbo].[vpams_temp]

    FROM [ASSESS].[dbo].[Property]

    WHERE parcelstat='F' 

    GO

The problem is in the first part of this where the concatenation occurs. I am attempting to convert this string (11-1111-11111.000) into this number (11111111111000). If they put their data in correctly, there is punctuation in exactly the correct spots and numbers in the right spots. If they make a mistake, then I end up with punctuation in the wrong spots and it creates a string that cannot be converted into a number.

Upvotes: 0

Views: 149

Answers (4)

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

simply, use the next code:-

declare @var varchar(100)
set @var = '11-1111-11111.000'
select convert(bigint, replace(replace(@var,'-',''),'.',''))

Result:-

11111111111000

Upvotes: 0

Mihai Ovidiu Drăgoi
Mihai Ovidiu Drăgoi

Reputation: 1317

How about simply replacing "-" and "." with "" before CONVERT to BIGINT?

To do that you would simply replace part of your code with

SELECT CONVERT(BIGINT,REPLACE(REPLACE(pin,"-",""), ".","")) AS PARCEL, ...

Hope it helps.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270873

First, I would use replace() (twice). Second, I would use try_convert():

SELECT try_convert(bigint,
                   replace(replace(pin, '-', ''), '.', '')
                  ) as PARCEL,
       taxyear, subdivisn, township, propclass, paddress1, paddress2, pcity
INTO [ASSESS].[dbo].[vpams_temp]
FROM [ASSESS].[dbo].[Property]
WHERE parcelstat = 'F' ;

You might want to check if there are other characters in the value:

select pin
from [ASSESS].[dbo].[Property]
where pin like '%[^-0-9.]%';

Upvotes: 1

S3S
S3S

Reputation: 25152

Why not just:

select cast(replace(replace('11-1111-11111.000','-',''),'.','') as bigint)

Upvotes: 0

Related Questions