Convert type Nvarchar to int.. skip if string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Convert type Nvarchar to int.. skip if string?

Author

Topic

Shanew
Starting Member

20 Posts

Posted - 2009-05-06 : 11:16:53

Hello again,

I have table lets say look like this:
Fname - Lname - Age
Shane Becvar 34
Danny Hall 52
Jill Smith Z21a4

To start all fields are Nvarchar so that all records get imported including record 3 (Jill Smith Z21a4) with the odd value in AGE field.

Is there a way with an SQL statement to convert the AGE field to INT and if it fiends a value that it can’t convert then it will skip that field laving it Null or blank. Can this be done?

Thanks for the help!
Shane

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-06 : 11:24:10

you can use isnumeric

try


select age
,case when isnumeric(age) = 1 then convert(int,Age)
else convert(int,0)
end as AGEModified
from
Mytable a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-07 : 02:48:10

quote:
Originally posted by Vinnie881

you can use isnumeric

try

select age
,case when isnumeric(age) = 1 then convert(int,Age)
else convert(int,0)
end as AGEModified
from
Mytable a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Note that isnumeric() is not reliable
select isnumeric('12d1'),isnumeric('$'),isnumeric('-'),isnumeric(',')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources