|
|
Posted by Steve Kass on 08/16/06 04:59
The ANSI/ISO international standard for the SQL language
does not provide the function you want. Oracle chose to add
a proprietary nonstandard function for this (which seems not
to allow you to specify the specific number type, and there are
many), and SQL Server uses the ANSI/ISO CAST function and
requires a type.
SELECT CAST('20.55' AS DECIMAL(4,2))
You can (if you know the consequences) use implicit conversion:
SELECT 100.00*'20.55'
SELECT 10000.0000*'20.55'
SELECT 1E0*'20.55'
If you aren't happy with what SQL offers as a language, there are
plenty of other languages in the world of computing from which
you can choose another.
Steve Kass
Drew University
<dba_222@yahoo.com> wrote in message news:1155683228.310649.322550@h48g2000cwc.googlegroups.com...
> Hmm. The question remains the same. (Not, why do I want to do that,
> or, what's wrong with what you already have? Or, you shouldn't do
> that.)
>
> I want a SIMPLE sql server function like to_number(), or str()
> To convert a string to a number.
>
> Does a simple function exist in sql server to do the conversion?
> What is the secret?
>
> Thanks
>
> David Portas wrote:
>> 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]
|