HELP!!! how to convert a string to number?

[sqlgrade1]

hi, can somebody help me to solve this problem?

first, declare 2 variables

declare @num int
declare @str varchar(255)

then set the varchar variable to '100/10'
set @str = '100/10'

is there anyway to assign a value to @num using @str, so that @num has a value 10??
set @num = @str ????????

 

[tlbroadbent]

You can use sp_executeSQL to execute a query and return a result. Here is an example of stored procedure that accepts a string, evaluates the expression in the string and returns the result as a decimal number.

Create Procedure spEvaluateExpression
@str nvarchar(20), @num Decimal(18,6) output
As

set nocount on

declare @sql nvarchar(1000)
set @sql='Select @num=1.0*'+@str

exec sp_executesql
@sql,
N'@num Decimal(18,6) Output',
@num=@num Output

return

----------------------------------

Execute the procedure like this...

Declare @nmbr Decimal(18,6)

Exec spEvaluateExpression
@str = '100/10',
@num = @nmbr output

Select Nmbr = @nmbr

----------------------------------

Of course, you'll want to add error checking to the T-SQL code. If you use SQL 2000, you may want to convert the SP to a user-defined function (UDF). 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.