iserror for failed convert? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

iserror for failed convert?

Author

Topic

coders4hire
Starting Member

4 Posts

Posted - 2005-10-29 : 15:15:10

I have an import conversion that's failing because the convert is failing. The reason I am using this conversion is because there are leading zeros I must eliminate in the field.
[CODE]
CONVERT(CHAR(12),CONVERT(DECIMAL(10,6),SUBSTRING(rec,190,12))) AS lat,
CONVERT(CHAR(12),CONVERT(DECIMAL(10,6),SUBSTRING(rec,178,12))) AS long
[/CODE]
If the convert fails, I would like to just leave the field blank. Is there an ISERROR function with MSSQL?

Thanks!
Doug

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-29 : 15:41:07

You can do this by changing the datatype of field in the destination table. This transformation change will make only numbers to be inserted.

Or else one of the nasty way of doing will be change the datatype in the source table.

Regards
Sachin

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-29 : 16:31:03

You can also try:

SELECT Replace(ltrim(Replace(SUBSTRING(rec,190,12),'0', ' ')), ' ', '0') AS lat

It will change all zeros to spaces, trim the spaces from the left, then put back any embedded zeros.

coders4hire
Starting Member

4 Posts

Posted - 2005-10-29 : 17:07:38

quote:
Originally posted by robvolk

You can also try:

SELECT Replace(ltrim(Replace(SUBSTRING(rec,190,12),'0', ' ')), ' ', '0') AS lat

It will change all zeros to spaces, trim the spaces from the left, then put back any embedded zeros.

Thanks, I'll need to modify this for negative numbers, but it will work!

Thanks!
Doug

coders4hire
Starting Member

4 Posts

Posted - 2005-10-29 : 17:08:33

quote:
Originally posted by sachinsamuel

You can do this by changing the datatype of field in the destination table. This transformation change will make only numbers to be inserted.

Or else one of the nasty way of doing will be change the datatype in the source table.

Regards
Sachin

I tried this originally and it errors out. I believe there may be some records with bad data in them.

Thanks!
Doug

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-30 : 01:51:26

What is the error?

regards
Sachin

coders4hire
Starting Member

4 Posts

Posted - 2005-10-30 : 14:27:27

quote:
Originally posted by sachinsamuel

What is the error?

regards
Sachin

It was a conversion failure... not sure of the exact error but it halted the update to the field type.

Thanks!
Doug

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources