Coverting from CHAR(65) datatype to INT 1

[awholtsIT]

WHat is the appropriate syntax to convert a field from a char(65) to a int datatype?

I need to convert the following field [Quantity] in the following line;

Min(Case When PT_UD_Number = 5 Then LongName End) As [Quantity]

Thanks in advance,

Andrew

 

[gmmastros]

Min(Case When PT_UD_Number = 5 Then [!]Convert(Int,[/!] LongName[!])[/!] End) As [Quantity]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Min(Case When PT_UD_Number = 5 Then LongName End) As [Quantity]

Maybe I'm missing something here, but is [LongName] a CHAR/VARCHAR type? If that is true and contains non-numeric characters, it will not convert to an INT.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[bborissov]

Can you give us some examples what you have in table?
Are you sure that "LongName" has only one char in it - CHAR(65)?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[awholtsIT]

I get the following error;

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.

I think it's because the values in this field are mostly numbers that are in as strings (25 as char(65)), but, there are also true string values. Is there a way to have those convert to either null or default to 0?

End result is to have all the "string" numbers become true integers and all the generic string values default to either null or 0. Perhaps a range function as all the "string" numbers are between 1 and 200?

Thanks for your reply.

Andrew

 

[gmmastros]

Select Min(Case When PT_UD_Number = 5 And [!]IsNumeric(LongName) = 1[/!] Then Convert(Int, LongName) End) As [Quantity]

IsNumeric will test to see if the value can be converted to ANY number. If it can, IsNumeric returns 1, otherwise, it returns 0.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awholtsIT]

Yes.

I will give this a try and respond shortly.

Many thanks again,

Andrew

 

[awholtsIT]

That worked perfectly.

Many Thanks,

Andrew