Archives of the TeradataForum

Message Posted: Thu, 28 Mar 2002 @ 16:02:17 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Carmen,

I am reading this to mean you have a column with numeric and alphanumeric data stored as character data type. As you cannot mix data types within a column, it sounds like to accomplish what you want to do that you will have to filter your return set to exclude the alphanumeric values so that you can accomplish the cast. If you need to see all the records in your return set, then I don't see a way to do the cast.

So your sql will look something like:

select cast (col1 as integer)
from tableA
where "col1 data could be numeric".

There isn't an easy function to determine if "col1 data could be numeric". The best I can suggest is to use the position function and then look for the possible alpha characters. If there is one common character in all the alpha values this is not to bad. But this could get ugly if there are no patterns and any alpha character could be present.

But it would look like:

where col1 is not null
and position ('a' in col1) = 0
and position ('A' in col1) = 0
and position ('b' in col1) = 0
and position ('B' in col1) = 0
etc...

Or if the presence of a number could guarantee you a numeric value then you could just only have 10 clauses like:

where position ('0' in col1) > 0
or position ('1' in col1) > 0
or position ('2' in col1) > 0
or position ('3' in col1) > 0
etc...

Best of luck to you if you have many records to scan or many alpha values to list. Hopefully, someone else will have a better suggestion.

--Jason Fortenberry
Argosy Gaming Company

	<Prev	Next>		<<First	<Prev	Next>	Last>>