|
|
Posted by David Portas on 08/15/06 22:19
dba_222@yahoo.com wrote:
> Dear Experts,
>
> Ok, I hate to ask such a seemingly dumb question, but I've
> already spent far too much time on this. More that I
> would care to admit.
>
> In Sql server, how do I simply change a character into a number??????
>
> In Oracle, it is:
>
> select to_number(20.55)
> from dual
>
> TO_NUMBER(20.55)
> ----------------
> 20.55
>
> And we are on with our lives.
>
>
> In sql server, using the Northwinds database:
>
> SELECT
> r.regionid,
> STR(r.regionid,7,2) as a_string,
> CONVERT(numeric, STR(r.regionid,7,2)) as a_number,
> cast ( STR(r.regionid) as int ) as cast_to_number
> FROM REGION R
>
> 1 1.00 1 1
> 2 2.00 2 2
> 3 3.00 3 3
> 4 4.00 4 4
>
>
>
>
> SELECT
> r.regionid,
> STR(r.regionid,7,2) as a_string,
> CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,
> cast (STR(r.regionid,7,2) as numeric ) as cast_to_number
> FROM REGION R
>
> 1 1.00 1 1
> 2 2.00 2 2
> 3 3.00 3 3
> 4 4.00 4 4
>
>
>
> Str converts from number to string in one motion.
>
> Isn't there a simple function in Sql Server to convert
> from string to number?
>
> What is the secret?
>
> Thanks
I don't really understand your problem. CAST does what you want and has
the advantage of being Standard SQL (unlike TO_NUMBER). STR does
something different and isn't needed in this case.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|