Changeing varchar to int

[DwayneL]

Hello there, I have been trying to use cast and convert but with no luck.

Is there any way to change a varchar to an int

like

"OOO1" and change that to 1 with out getting a null value?

 

[tlbroadbent]

The following should work.

Convert a literal:
Cast('0001' As Int)
Convert(Int, '0001')

Convert a variable:
Declare @var varchar(8)
Set @var='2399'
Cast(@var As Int)
Convert(Int, @var)

Convert a column:
Cast(varcharCol As Int)
Convert(Int, varcharCol)

If you can't get these to work, please post your SQL code, a sample of the source data and the output of the SQL statment including error or warning messages. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[imars]

Dwaynel, this is implicitly on the language. You only need to assign the string value to the variable of type number

Ex declare @x int
set @x = '001'
select @x

Result --->> 1

 

[tlbroadbent]

SQL will implicitly make some conversions if possible. However, when selecting the columns in a query you must explicitly convert them to the desired data type. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[DwayneL]

Thanks for all the help,

I thought you had to set to keep the new value on :
Cast(@var As Int),
Convert(Int, @var)

I never knew it actually changed the variable type, i was under the impression that it returned a value.

Once again, Thanks. I get more out of this place then my from Education.

 

[tlbroadbent]

Cast and Convert don't change the data type of the variable, column or literal. The data type of the output is whatever you've declared it to be.

I'm not sure what you mean by "I thought you had to set to keep the new value on:".
Cast(@var As Int),
Convert(Int, @var) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.