DataType converting, char to float

I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.

Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..

Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0

/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/

--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)

--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.

The following post discusses this problem:

post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:

Dim InsertQuery As String
Set oConn = New Connection

Set oRec = New Recordset
oRec.Open InsertQuery, oConn

is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?

-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"

Is a test-query I use

And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.

create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.

DataReader Source and Column Types

Is there a way to control the types for output columns of a DataReader Source? It appears that any System.String will always come out as DT_WSTR. As I have my own managed provider, and I know what went in, I can say that really it should be DT_STR. The GetSchemaTable call from my provider will always say System.String as it does not have much choice, but GetSchemaTable does contain a ProviderType which is different for my DT_STR vs DT_WSTR, or rather when I want each. I think something like MappingFiles as used by the Wizard would work, but can I do anything today?

Darren,

I am afraid there is no way to influence this mapping. The Data Reader Source adapter uses only the CLR type (the DataType column from the table's schema) to determine which DT_... type to choose. The ProviderType field could not be used as it has different meaning for different providers.

The mapping files would definitely help here, but that infrastructure is not used by this component.

I do not have any good advice, but explicit data conversion to the DT_STR type or building your custom ADO .NET adapter are options I see available at this moment.

Thanks.

|||

Bob,

Thanks for confirming what I already suspected, but I had to ask. I'm thinking a MSDN feedback request for the ability to supply mapping files would be coming your way.

Conversion works, but I'm concerned about the impact of doubling the buffer size each time I do this. Most columns I am working with are DT_WTR, but need to be DT_STR, it is 2 x buffer every time. Is this really twice the size or is there some fancy pointer type work going on? I will probably test when I have time as I had some other ideas about custom components for such conversion, but it depends on what the impact really is.

A custom provider had been considered but currently rejected due to time. It took me long enough to get the managed provider written :)

Thanks

|||

Hi Darren,

I believe you are right about the buffer size. It might impact your package performance, but it is not sure how significant that could be. It may depend on many factors. If you get a chance to measure the impact in your configuration, please share results with us.

If the "power" stays with us, we should be able to provide much better story with managed providers in the next version.

Thanks.

|||

I have done some playing around with this.

For information my theory goes like this. If I use a Data Conversion transform I am increasing the number of columns in my buffer so I get less rows per buffer. This seems inefficient. On the other side we know that copying data between buffers has a cost. So lets test which is more efficient, the larger row size versus the cost of moving between buffers, and keeping a small row size.

I wrote a simple asynchronous component that allowed you to select columns from the input buffer which are then copied directly to the output buffer. The one feature is that any DT_WSTR column is reproduced as DT_STR. So the buffer sizes/structure are the same for input and output except for the change in type, and any associated overheads of each type. One would think that unicode types require twice the space of non-unicode, so this should make the asynchronous component test even faster as this allows even more rows to fit into the output buffer of my component.

For a baseline I used a Script Component -> Union All. The script component generated a variable number of rows, as determined by a package variable. The columns produced are 1 integer column (row count), and 9 x 50 character DT_WSTR columns fully populated.

For testing I used the same script component and two methods of converting the columns -

Script Component -> Data Conversion -> Union All

Script Component -> DeUnicodeAsynchTestComponent -> Union All

Tests showed that the data conversion was 1.5-2.5 times slower than the baseline. The asynchronous component was then 2-2.5 times slower than the data conversion. Times were averaged across 6 executions. The range in times are for different row counts, 100,000 to 1,000,000.

N.B. These ratios are for my local machine, and I would fully expect results to vary on different hardware and with different resource constraints. These are for illustration only. If you want to know how this equates to your environment, test it for yourself, and use real hardware, not a test system.

So, whilst it may not look pretty leaving the buffer alone is the way to go. Trying to remove columns or change columns is a non-starter as this means creating a new buffer, the cost of which far outweighs the benefit of the smaller row size in the buffer. When you do need to work on columns, use a synchronous component such as the Data Conversion or Derived Column transformations, and don’t worry if you end up with more columns that you will use at the end. (Obviously don’t create columns for the sake of it!)

Datalength of unicode and non-unicode types?

I'm using SQL Server 2000. Suppose I'm in Northwind database and
I execute the following query:
SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
The results of 'Text length' shows 2 X total characters because
Notes is of type ntext (Unicode type).
Q: How to form a query that show the total characters used(in this case
notes/2) provided I'm not sure about the underlying datatype whether
it's Unicode or otherwise?
How to check underlying datatype of a column using T-SQL programmatically?
Regards,
Pedestrian
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1If you know the column type is some string type, you
can try this. I'm guessing that the 0-length substring
calculation will be relatively painless:
SELECT
Notes,
Datalength(Notes)
/CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
THEN 2 ELSE 1 END
FROM Employees
Steve Kass
Drew University
pedestrian via webservertalk.com wrote:

>I'm using SQL Server 2000. Suppose I'm in Northwind database and
>I execute the following query:
>SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
>The results of 'Text length' shows 2 X total characters because
>Notes is of type ntext (Unicode type).
>Q: How to form a query that show the total characters used(in this case
>notes/2) provided I'm not sure about the underlying datatype whether
>it's Unicode or otherwise?
>How to check underlying datatype of a column using T-SQL programmatically?
>Regards,
>Pedestrian
>
>|||If you just want to count number of characters, use len instead of
datalength. This works for both Unicode and non-Unicode strings.
You can query column information in T-SQL by using the
INFORMATION_SCHEMA.COLUMNS view,
HTH
- Baileys
pedestrian via webservertalk.com wrote:
> I'm using SQL Server 2000. Suppose I'm in Northwind database and
> I execute the following query:
> SELECT Notes, Datalength(Notes) As 'Text length' FROM Employees
> The results of 'Text length' shows 2 X total characters because
> Notes is of type ntext (Unicode type).
> Q: How to form a query that show the total characters used(in this case
> notes/2) provided I'm not sure about the underlying datatype whether
> it's Unicode or otherwise?
> How to check underlying datatype of a column using T-SQL programmatically?
> Regards,
> Pedestrian
>|||Baileys wrote:
> If you just want to count number of characters, use len instead of
> datalength. This works for both Unicode and non-Unicode strings.
However, you need to keep in mind that LEN() excludes the trailing
blanks when counting the number of characters (but DATALENGTH() does
not).
Razvan|||Steve Kass wrote:
> If you know the column type is some string type, you
> can try this. I'm guessing that the 0-length substring
> calculation will be relatively painless:
> SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
> FROM Employees
Hello, Steve
That's a brilliant trick.
However, I'm not sure why you have used the CASE expression ?
Consider this query:
SELECT
Notes,
Datalength(Notes)
/ DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
)
FROM Employees
Wouldn't this be the same as your query ?
Razvan|||
Razvan Socol wrote:

>Steve Kass wrote:
>
>Hello, Steve
>That's a brilliant trick.
>However, I'm not sure why you have used the CASE expression ?
>Consider this query:
>SELECT
> Notes,
> Datalength(Notes)
> / DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
)
>FROM Employees
>Wouldn't this be the same as your query ?
>Razvan
>
>
Yup. Good catch.
SK|||
Baileys wrote:

> If you just want to count number of characters, use len instead of
> datalength. This works for both Unicode and non-Unicode strings.
But LEN does not accept types ntext and text, which the user required.
SK
> You can query column information in T-SQL by using the
> INFORMATION_SCHEMA.COLUMNS view,
> HTH
> - Baileys
> pedestrian via webservertalk.com wrote:
>|||oops, I missed that part of the question...
- Baileys
Steve Kass wrote:
>
> Baileys wrote:
>
>
> But LEN does not accept types ntext and text, which the user required.
> SK
>|||Thanks for quick replies.... particularly to Steve Kass 'n Razvan Socol ...
.
Best Regards,
Pedestrian
Steve Kass wrote:
>If you know the column type is some string type, you
>can try this. I'm guessing that the 0-length substring
>calculation will be relatively painless:
>SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
>FROM Employees
>Steve Kass
>Drew University
>
>[quoted text clipped - 14 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Confused over the following query which return the result 2s:
SELECT Datalength(space(1)+SUBSTRING(Notes,1,0)
) as myCol from employees
Why not the above query return 1s instead of 2s ... ?
I suppose space(1) returns 1 and SUBSTRING(Notes,1,0) as below return 0 as
below...
hence space(1)+SUBSTRING(Notes,1,0) should only returns 1.
This query return me 1... Ok
SELECT Datalength(SPACE(1)) As Slength
This query return me 0s... No problem...
SELECT Datalength(SUBSTRING(Notes,1,0) ) As Length1 FROM Employees
Steve Kass wrote:
>If you know the column type is some string type, you
>can try this. I'm guessing that the 0-length substring
>calculation will be relatively painless:
>SELECT
> Notes,
> Datalength(Notes)
> /CASE WHEN DATALENGTH(SPACE(1)+SUBSTRING(Notes,1,0)
) = 2
> THEN 2 ELSE 1 END
>FROM Employees
>Steve Kass
>Drew University
>
>[quoted text clipped - 14 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

databases

i really want to know,
there are how types of databases?
how many types of servers?
how many types of inernet based languages?lipuni wrote:

Quote:

Originally Posted by

i really want to know,
there are how types of databases?
how many types of servers?
how many types of inernet based languages?
>

42|||"Jonathan Roberts" <gremln007@.diynics.comwrote in message
news:ePjzh.399$OY.332@.newsfe20.lga...

Quote:

Originally Posted by

lipuni wrote:

Quote:

Originally Posted by

>i really want to know,
>there are how types of databases?
>how many types of servers?
>how many types of inernet based languages?
>>

>
42

That is exactly what I was going to post, you beat me to it...

http://en.wikipedia.org/wiki/42_(number)