select cast(substring(ccfccard , 1, 5) as numeric)
from emp0
where ccfccard like '[0-9][0-9][0-9][0-9][0-9]%'

Best Regards,

Mihail Todorov
System Analyst
ICT Department
Kamenitza AD



-----Original Message-----
From: Johnson Shaunn
[mailto:mssqldba-ezmlmshield-x71130526.[Email address protected]
Sent: Wednesday, March 30, 2005 4:38 PM
To: LazyDBA Discussion
Subject: example using cast or convert?


Howdy -

I'm trying to find correct syntax to convert a text column to numeric. I've
tried examples like so -


[snip examples]

select convert(numeric, ccfccard) from emp0
where ccfccard between '10980000002' and '10990000000'

**error: Server: Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type text to numeric is not allowed.

- also -

select convert(numeric, convert(varchar (50), ccfccard)) from emp0
where ccfccard between '10980000002' and '10990000000'

**error: Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

[/snip examples]


There was one attempt where I was able to get something like 'can't convert
directly from text' (sorry, I forgot to copy the error message).

Can someone send me a doc to give examples of how to convert or cast text
fields into numeric format *without* having to change the database / field?

Thanks!

-X



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]

MS Sql Server LazyDBA home page