convert string data to int

Question

convert string data to int

ps.

One Orange Chip

Points: 29423
More actions
September 19, 2011 at 12:04 pm

#257747

i am trying to import an access table in sql server. One of the columns Age is defined as Integer in SQL Server.
In Access, this feild has non-numeric values in some rows. I need to either enter 0 where there is non-numeric value or i need to replace it with some integer.
pls assist.

Pradeep Singh

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 1

It depends! And there is no standard formula as per my knowledge. But I would prefer to import access table in a dummy sql table with varchar type of Age field and then perform all the update script with the help of

ISNUMERIC() function (to find all non numeric values). and then transfer data to the original table and then drop the dummy table.

Regards,

Sudhir

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 2

You can do it in SSIS by putting a Data Conversion component that converts the strings to int.

Map the error rows to a derived column that replaces the strings with a 0, and then merge the two flows together using a UNION ALL.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Tim Mitchell SSCoach Points: 15685 More actions · Answer 3

Either the Data Conversion or the Conditional Split would work for this. Try to convert the value to the appropriate type, similar to:

(DT_I4)[input_column_name]

If the cast is successful, the integer value would be added to the data pipeline. IF not, the conversion would fail and send the row to the component's error output. Make sure you configure the component to "Redirect Rows to Error Output" rather than "Fail Component", since the latter is the default behavior.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Phil Parkin SSC Guru Points: 246992 More actions · Answer 4

Sudhir's idea seems the least cumbersome to me ...

Or (and I am not sure whether this is even possible, because I have not tried importing from Access for ages...) use an Access query as your data source and do the required conversions and substitutions there.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ps. One Orange Chip Points: 29423 More actions · Answer 5

I imported the access db in a staging database. From there all conversions were smooth. database didnt have huge number of records so used normal select into statement with conditions 🙂

Pradeep Singh