select cast error

[alexqa2003@yahoo.com]

name1 field is is nvarchar(40).

(1)select case when isnumeric(name1 ) = 1 then
cast(name1 as int) else null end as name

In (1) when name1 is not numeric, name is null and
its type becomes number not string(40).

And,

(2)select case when isnumeric(name1 ) = 1 then
cast(name1 as int) else name1 end as name

In (2) when name1 is Not numeric it gives error:
can not cast name to int.

Basically, i like to convert name1 to Integer if it is
numeric or keep it in its origianl nvarchar(40) if it
is Not numeric. how?.





--
Sent by 3 from yahoo element from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com

[jobi]

Re: select cast error

case when name1 is null then ....

when a column is null, it has no contents/value , so you'll have to evaluate
null !

jobi
"alexqa2003@yah oo.com" <u128845214@spa wnkill.ip-mobilphone.net> wrote in
message news:l.10657641 93.1133178710@[63.127.215.130]...[color=blue]
> name1 field is is nvarchar(40).
>
> (1)select case when isnumeric(name1 ) = 1 then
> cast(name1 as int) else null end as name
>
> In (1) when name1 is not numeric, name is null and
> its type becomes number not string(40).
>
> And,
>
> (2)select case when isnumeric(name1 ) = 1 then
> cast(name1 as int) else name1 end as name
>
> In (2) when name1 is Not numeric it gives error:
> can not cast name to int.
>
> Basically, i like to convert name1 to Integer if it is
> numeric or keep it in its origianl nvarchar(40) if it
> is Not numeric. how?.
>
>
>
>
>
> --
> Sent by 3 from yahoo element from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com[/color]

[Erland Sommarskog]

Re: select cast error

alexqa2003@yaho o.com (u128845214@spa wnkill.ip-mobilphone.net) writes:[color=blue]
> name1 field is is nvarchar(40).
>
> (1)select case when isnumeric(name1 ) = 1 then
> cast(name1 as int) else null end as name
>
> In (1) when name1 is not numeric, name is null and
> its type becomes number not string(40).[/color]

Note that isnumeric may return 1, and yet the cast may fail. This
would happen if name has a value like '$5' or '3.34'. isnumeric()
is basically a useless function, because you cannot find out to
which numeric types the string is converible to.

To test for integer value, you can try:

name1 NOT LIKE '%[^0-9]%'

although neither this is fool-proof. This experssion does not handle
signed numbers, and but will accept numbers which does not fit an int.
[color=blue]
> (2)select case when isnumeric(name1 ) = 1 then
> cast(name1 as int) else name1 end as name
>
> In (2) when name1 is Not numeric it gives error:
> can not cast name to int.[/color]

Yes, the result of a CASE expression is always the same type, and the
type is defined by the first type of the expression of the first WHEN
branch. All other WHEN branches are converted to that type.
[color=blue]
> Basically, i like to convert name1 to Integer if it is
> numeric or keep it in its origianl nvarchar(40) if it
> is Not numeric. how?.[/color]

I'm afraid that this request does not make that much sense. All values
in a column must have the same type.

However, there is the type sql_variant. If you use

cast(cast(name1 as int) as sql_variant)

all values will be of the type sql_variant, but as a property retain the
original type.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp