CONVERTING A STRING TO INT OR NUMBER??? 1

[SQLRory]

Hello,

I am having problems with a field in our data base called
tpot.u_months_unemployed. This field is a string lengh 4 however only contains numeric characters.

Due to the field being a string I cannot ranges in Crystal Reports XI due to the field data type.

For example the command
({Command.Months unemployed (at reg)} IN ("0" to "5"))
doesn't work in Crystal.

I have tried to CAST (tpot.u_months_unemplyed AS INT(4)) in the SQL statement however this doesn't work.

Any ideas on how to convert this string to a integer?

Thanks

 

[mrdenny]

If all the data in the column is numeric then cast would be the correct method.

What error message is given when you use the CAST command?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLRory]

Hi there,

The data in the field should all be numerical however some data is alpha numeric. I think this is the problem.

The are examples where the data is 5+, 0-5, 6-9, 9yrs etc
This is due to the nature of the original context of the field being 'Months Unemployed'.

I cannot however write a function on the database due to restricted access because of db restrictions. I can however run update functions and could therefore cleans the data to make it all numerical and then convert the shema of the column.

Or if you have a better easier idea that would be great.

Thnaks

Rory

 

[mrdenny]

The first thing you'll want to do then is clean the data in the table, then write your report. You should also modify the front end app so that it only accepts a number, then have the DBA convert the column from a character string into a numeric string (INT probably).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog