Converting delimited varchar @parameter for use in NOT IN()

I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:

> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?

Converting Date-Time to Date Conundrum

I need to be able to convert a Date-Time Field to a Date Fied in my SQL
Tables.
Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
but because it converts the date value to a string I can say goodbye to
localization.... any ideas on how to convert the field but still allow
localization.Try this function...
CREATE FUNCTION [dbo].[fnRemoveTimeFromDateTime] (@.InputDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @.OUTPUT AS SMALLDATETIME
SET @.OUTPUT = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.InputDate)))
RETURN @.OUTPUT
END
"SAcanuck" wrote:
> I need to be able to convert a Date-Time Field to a Date Fied in my SQL
> Tables.
> Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
> but because it converts the date value to a string I can say goodbye to
> localization.... any ideas on how to convert the field but still allow
> localization.

Converting Datetime from the Varchar value

I am not sure if this is the correct forum to post to for this but,

I have a stored procedure in the code like so:

dim calensql as string = "sp_scheduleworkfromcal '" & sun & "', '" & mon & "', '" & tue & "', '"
& wed & "', '" & thu & "', '" & fri & "', '" & sat & "', '" & Label1.Text & "', '" & Label2.Text & "', '"
& Label3.Text & "', '" & Label4.Text & "', '" & Label5.Text & "', '" & Label6.Text & "', '" & Label7.Text & "', " & "129"

and then in my stored procedure I have

CREATE PROCEDURE sp_scheduleworkfromcal
(@.sun VarChar(50), @.mon VarChar(50), @.tue VarChar(50), @.wed VarChar(50), @.thu VarChar(50),
@.fri VarChar(50), @.sat VarChar(50), @.dsun VarChar(50), @.dmon VarChar(50), @.dtue VarChar(50),
@.dwed VarChar(50), @.dthu VarChar(50), @.dfri VarChar(50), @.dsat VarChar(50), @.userid int)
AS
Declare @.store datetime
If @.sun != '' begin
 Update servicerequests set date_scheduled=(Convert(datetime, @.dsun)) where trackingnumber=@.sun
 Select @.store = rtrim(retailer) + ' ' + rtrim(storeNumber) from servicerequests where trackingnumber = @.sun
 UPDATE CalendarSchedule SET cal_notes=@.store WHERE cal_date=@.dsun AND userid=@.userid
 IF @.@.ROWCOUNT = 0
 INSERT INTO CalendarSchedule (userid, cal_date, cal_notes) VALUES (@.userid, @.dsun, @.store)
End

and I am getting the error something like
Syntax error converting datetime from character string.

If I change the parameters in the stored procedure to datetime or varchar and get rid of the single quotes, I get incorrect sytax near "/".

I am tracking the sql statement to see where I can fix the problem, but cannot come up with a solution.

can anyone help me out with this one??
Thanks
EricI think the date format you are trying to create is wrong ... At the location ... Try investigating the line :
@.store = rtrim(retailer) + ' ' + rtrim(storeNumber)|||I don't know whether I should use datetime in the values or varchar.
In the two tables
The one the field is a datetime field and in the other table it is a Char(15)

why won't it recognize the sql statement
SQL Statement sp_scheduleworkfromcal '897', '', '', '', '903', '', '', '10/19/2003', '10/20/2003', '10/21/2003', '10/22/2003', '10/23/2003', '10/24/2003', '10/25/2003', 129

thats the trace.

E|||I have tried a variation of your code and am not receiving any errors.

Where exactly are you getting the error? Which line number, and what is the error message exactly?

What are the data types and lengths of the following columns?
-- servicerequests.date_scheduled
-- servicerequests.trackingnumber
-- CalendarSchedule.cal_date

Terri|||servicerequests.date_scheduled datetime(8)
servicerequests.trackingnumber bigint(8)
CalendarSchedule.cal_date datetime(8)

I am getting the error on the
cmd.ExecuteNonQuery() line
and the error is as follows:
System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I changed the values of the textboxes from the page in the sproc from VarChar(50) to datetime as well as the data type in the CalendarSchedule table from Char(15) to datetime and still am getting the same error.

You say you tried a variation of the code? what about it did you change?
are my datatype's wrong?

Thanks
E|||I mean labels from Varchar(50) to datetime - not textboxes

Converting dates.

Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?

It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)

i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?

SELECT * FROM Table WHERE ISDATE(DateCol)=0

??

SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return

what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2

mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed

unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...

:)|||sounds like the attitude of a certain large software company which shall remain nameless...

yea, they drill it into you, it takes a while to feel clean again. :)

did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.

How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.

I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.

There are few ways to update your DATETIME columns...

1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.

Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).

By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.

Converting date to Varchar? and Varchar to Date?

I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'

What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?

I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar

This should give you an idea of how to handle these conversions:

DECLARE @.MyDateTimeValue datetime
SET @.MyDateTimeValue = '2006-03-26 00:00:00.000'

SELECT convert( varchar(10), @.MyDateTimeValue, 101 )

-
03/26/2006

SELECT cast( '03/26/2006' AS datetime )

2006-03-26 00:00:00.000

converting Datatypes:

Hi All,

how do you convert from a date to an int ? as well as converting from Varchar to and Int

in SQL server 2000 ?

I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e

DECLARE @.DateValue AS VARCHAR(20)

SELECT @.DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'

print convert(int,@.DateValue) - 5
print GetDate() - 35

SELECT ccy_code, NULL, xrate_date, sterling_xrate
FROM SylvanTrans.dbo.SIADHP_XRate_Hist
WHERE xrate_date >= (CONVERT(int, @.DateValue) - 35 )
ORDER BY xrate_date

now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @.DateValue Variable, but the conversion throws a syntax error:

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'GetDate()' to a column of data type int.

what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!

thanks

regards

Hi,

why don′t use use the associated function for dealing with datetime like DATEADD ? You can also add negative dateparts to a datetime like
DATEADD(dd,-35,SomeDate)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply,

yes i did see that function, but i neet to have a seperate table which stores the GetDate() so that in my sotred procs

i can do a select on its location and do things like :

select * from tablename

where @.valueDate - 1 > 5

where @.valueDate has the getDate () from my Applicationsettings Table, if i use the DateAdd() i wont be able to do this you see...

|||I think I did not got your point. COuld you explain this in more detail ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Converting data question

I need to combine two columns and copy them to another. One column is varchar and one is decimal (3,0) and the destination column is int. The column of decimal contains only 1 and 2 digit numbers. I need to perface a 0 to the data with 1 digit before combining the two columns. If someone could guide me here, I'd appreciate it. I've been trying to use cast and convert with no success.Your destination column should be a varchar column if the column from your source varchar is not an integer. You can post a set of sample data and expected result here to help answer your question.|||Source Columns Destination
Varchar Dec int
0660 10 66010
0660 2 66002
0660 3 66003
0660 11 66011|||

Kinny:

I put together a quick mock-up of what I think you are trying to do. Do either of the output columns look like what you are trying to do?

Dave

-- --
-- This mock-up works correctly for "correct values" as discribed in the request.
-- However, it does not know what to do with most "error conditions." If you don't
-- have to worry about "incorrect values" this select should work fine
--
-- Problem areas:
--
-- 1. What do you do with nulls?
-- 2. What do you do with 3-digit numbers?
-- 3. What do you do with negative numbers?
-- --
set nocount on
declare @.mockUp table
( sampleString varchar (20) not null,
sampleDecimal decimal (3,0) null
)

insert into @.mockUp values ('testView', null) -- ? Do you have to worry about null?
-- ? If so, display double-zero for nulls?
insert into @.mockUp values ('testView', 0) -- O0; OK
insert into @.mockUp values ('testView', 4) -- 04; OK
insert into @.mockUp values ('testView', 21) -- 21; OK
insert into @.mockUp values ('nextSample', 99) -- 99; OK
insert into @.mockUp values ('negative', -1) -- -1; Not sure; is this a problem?
insert into @.mockUp values ('negative', -99) -- 99; Wrong; is this a problem?
insert into @.mockUp values ('tooBig', 100) -- 00; Wrong; is this a problem?
--select * from @.mockUp

select sampleString,
sampleDecimal,
sampleString + isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00')
as [String First],
isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00') + sampleString
as [Decimal First]
from @.mockUp

-- --
-- Sample Output:
-- --

-- sampleString sampleDecimal String First Decimal First
-- -- - - -
-- testView NULL testView00 00testView
-- testView 0 testView00 00testView
-- testView 4 testView04 04testView
-- testView 21 testView21 21testView
-- nextSample 99 nextSample99 99nextSample
-- negative -1 negative-1 -1negative
-- negative -99 negative99 99negative
-- tooBig 100 tooBig00 00tooBig

|||

Hi,

Here is the update statement for question:

UPDATE yourTable

SET DesINTColumn= CAST(CAST(CONVERT(int, VarCharColumn1) AS varchar)+ RIGHT('0' + CAST(DecimalColumn1 AS varchar), 2) AS INT)

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

sqlsql

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

Converting CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT!

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz AnsariIf you are, in batch, converting all types, you couls just script the database using Enterprise Manager, and then do a search and replace for each of the types. Be aware that converting from varchar to nvarchar could cause problems if rows already contain over 4000 characters.|||I have just created a small VB utility program which can take list of files (.sql files) and uses regular expression to replace data types in SPs files. I am still looking for some automated tool.

Regards

Converting a varchar to int

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.

But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9

A classic violation of first normal form:

http://en.wikipedia.org/wiki/First_..._ single_field
If at all possible, change your table to look like this:

Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

and then this simply becomes

SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)

That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.|||I can't change the actual table, but I can create a stored proc that
inserts it correctly into another table. I didn't even think to do
that (**duh**)! Thank you very much for your assistance!

Ed Murphy wrote:

Quote:

Originally Posted by

Patti wrote:
>

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:

Loan# Codes
11111 24-13-1
22222 1
33333 2-9

>
A classic violation of first normal form:
>
http://en.wikipedia.org/wiki/First_..._ single_field
>
If at all possible, change your table to look like this:
>
Loan# Code
11111 24
11111 13
11111 1
22222 1
33333 2
33333 9
>

Quote:

Originally Posted by

I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:

SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/

>
and then this simply becomes
>
SELECT Loan#
FROM Table1
WHERE Code in (2, 13, 1)
>
That said, if fixing the 1NF violation will take a while, then in the
short term, you can do something like the following. (You can't convert
Codes to int, because e.g. '24-13-1' isn't a number. Instead, you must
convert the search terms from int to varchar.)
>
SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'
>
Also, you may need SELECT DISTINCT, in case some Loan#s have multiple
matches and you only want to include them once.

|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

SELECT Loan#
FROM Table1
WHERE '-'+Codes+'-' like '-2-'
OR '-'+Codes+'-' like '-13-'
OR '-'+Codes+'-' like '-1-'

Seems like some % are missing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This might work:
SELECT Loan#
FROM Table1
WHERE patindex('%[2,13,1]%',Codes) 0

Patti wrote:

Quote:

Originally Posted by

I am struggling with converting a certain varchar column into an int.
I have a table that has 2 fields - one field holds the loan number and
the other field holds the codes associated with that loan number.
Here's some example data:
>
Loan# Codes
11111 24-13-1
22222 1
33333 2-9
>
I need to check the Codes field for certain code numbers. The Select
statement I'd like to use is:
>
SELECT Loan#
FROM Table1 WHERE Codes IN (2, 13, 1)
/*My desired results is that all loans from the above example would be
selected because they all have one of these codes*/
>
Of course I cannot use the above statement because the Codes field is a
varchar. And if I put single quotes around the numbers in my IN
statement I don't get the desired results; the fields with multiple
codes are excluded.
>
But how do I convert this varchar to an int? A simple convert or cast
statement doesn't work. I've looked all over the web to find how to do
this, but have not been able to figure it out. Any help would be much
appreciated.

|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>SELECT Loan#
>FROM Table1
>WHERE '-'+Codes+'-' like '-2-'
> OR '-'+Codes+'-' like '-13-'
> OR '-'+Codes+'-' like '-1-'

>
Seems like some % are missing.

Yes, of course you're right, should be

WHERE '-'+Codes+'-' like '%-2-%'
OR '-'+Codes+'-' like '%-13-%'
OR '-'+Codes+'-' like '%-1-%'

but the approach of "use a stored procedure to copy the data to a
better-normalized table" is probably better. (Oh, and that new
table should probably have an index on the Code column.)

Converting a string of binary numbers to a binary datatype

I have a varchar field which holds numeric (binary) data e.g. '00101111' and I want to convert this to a binary data type with the value e.g. 0x00101111

But when I try the following SQL:

select top 5 flag2,convert(binary,flag2) flag2_as_binary from my_table

I get:

flag2 flag2_as_binary
--- -------------------
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000

(5 row(s) affected)

I want some SQL that will return the following (with flag2_as_binary as a real binary datatype):

flag2 flag2_as_binary
--- -------------------
00000000 0x00000000
00000000 0x00000000
00000000 0x00000000
00000100 0x00000100
00000100 0x00000100

(5 row(s) affected)

Thanks.The sql binary datatype is actually displayed hexidecimal base 16 usinge characters 0-9 and A-F, not base 2.

converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury

Converting a field from int to varchar during query

I have a query where I am writing the following statement:
select * from .....
where table1.field1 = table2.field2
....
....
....
field2 is a varchar field and field1 is an int field. Field2 contains the
data mapping data for field1 and also other unrelated data as character
strings.
How do I run this query without getting the error - "Syntax error converting
the varchar value 'XXXX' to a column of data type int."
Thanks,
Jignesh.
On Wed, 2 Feb 2005 13:07:04 -0800, Jig Bhakta wrote:

>I have a query where I am writing the following statement:
>select * from .....
>where table1.field1 = table2.field2
>...
>...
>...
>field2 is a varchar field and field1 is an int field. Field2 contains the
>data mapping data for field1 and also other unrelated data as character
>strings.
>How do I run this query without getting the error - "Syntax error converting
>the varchar value 'XXXX' to a column of data type int."
Hi Jignesh,
Try:
select * from .....
where CAST(table1.field1 AS varchar(10)) = table2.field2
....
....
....
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Nope, still getting the error.
"Hugo Kornelis" wrote:

> On Wed, 2 Feb 2005 13:07:04 -0800, Jig Bhakta wrote:
>
> Hi Jignesh,
> Try:
> select * from .....
> where CAST(table1.field1 AS varchar(10)) = table2.field2
> ....
> ....
> ....
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 3 Feb 2005 13:01:08 -0800, Jig Bhakta wrote:

>Nope, still getting the error.
Hi Jignesh,
In that case, you better post the complete query. Best is to include
CREATE TABLE statements and INSERT statements with some sample data to
reproduce the error.
Here's a small script that shows that my change does work on my system, so
your error is either caused by something else in your query, by something
strange in your data or by a SQL Server bug. If you post a script that
will reproduce the error, we can find which of these three is the cause.
-- Start of repro script that show the error is corrected
create table table1 (field1 int not null)
create table table2 (field2 varchar(20) not null)
go
insert table1 (field1) select 1 union all select 2
insert table2 (field2) select '1' union all select 'XXXX'
go
print 'Original'
print ''
select * from table1, table2
where table1.field1 = table2.field2
print '--'
go
print ''
print 'Corrected'
print ''
select * from table1, table2
where CAST(table1.field1 AS varchar(10)) = table2.field2
print '--'
go
drop table table1
drop table table2
go
-- Output:
Original
field1 field2
-- --
1 1
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'XXXX' to a column of data type
int.
Corrected
field1 field2
-- --
1 1
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Jig,
Try this:
select * from ...
where
case when table1.field1 not like '%[^0-9]%' then cast(table1.field1 as
int) end
= table2.field2
The reason for the error is that a varchar = integer comparison
causes the varchar to be converted to an integer, so XXXX is
getting converted to an integer for the comparison. The CASE
statement causes the query to compare the column field1 with
field2 only if field1 is all digits. Otherwise, it compares NULL, which
won't cause an error.
The best thing to do would be not to store numerical and character
data in the same column! If it makes sense to compare two columns,
but they are not the same type, it's usually a sign that the database design
can be improved.
Steve Kass
Drew University
Jig Bhakta wrote:

>I have a query where I am writing the following statement:
>select * from .....
>where table1.field1 = table2.field2
>...
>...
>...
>field2 is a varchar field and field1 is an int field. Field2 contains the
>data mapping data for field1 and also other unrelated data as character
>strings.
>How do I run this query without getting the error - "Syntax error converting
>the varchar value 'XXXX' to a column of data type int."
>Thanks,
>Jignesh.
>

converting a column from varchar to int

I need to copy the data from varchar column into an integer column,
obviously only copying integer values. How can I do this? I am not even
sure how to select data from a column so that the results are only the
numeric ones. Thanks for your help.Et,
ALTER TABLE should allow you to change the datatype of the table assuming
all values are integers.
You could run something like this:
UPDATE tablename
SET col2 = CAST(col1 AS int) --would require the values to be integers not
characters
Check out the ISNUMERIC function for differentiating between integers and
characters. Also see:
http://www.aspfaq.com/show.asp?id=2390
HTH
Jerry
"et" <eagletender2001@.yahoo.com> wrote in message
news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I need to copy the data from varchar column into an integer column,
>obviously only copying integer values. How can I do this? I am not even
>sure how to select data from a column so that the results are only the
>numeric ones. Thanks for your help.
>|||Yes, the isnumeric function worked, the cast of course gave me an error
because not all values are integers. This is what I did:
update tbl set newcol=originalcol
where isnumeric(originalcol)=1
Now I have to populate the remaining fields with an integer, any idea an
easy way to do that? I'd like to do it starting with the next available
number, but it doesn't really matter what the number ends up being. This
column will eventually be an identity column.
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
> Et,
> ALTER TABLE should allow you to change the datatype of the table assuming
> all values are integers.
> You could run something like this:
> UPDATE tablename
> SET col2 = CAST(col1 AS int) --would require the values to be integers
> not characters
> Check out the ISNUMERIC function for differentiating between integers and
> characters. Also see:
> http://www.aspfaq.com/show.asp?id=2390
> HTH
> Jerry
>
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||ok...clarify please...IDENTITY is system generated so you don't populate it
with any values. "any number"?
"et" <eagletender2001@.yahoo.com> wrote in message
news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
> Yes, the isnumeric function worked, the cast of course gave me an error
> because not all values are integers. This is what I did:
> update tbl set newcol=originalcol
> where isnumeric(originalcol)=1
> Now I have to populate the remaining fields with an integer, any idea an
> easy way to do that? I'd like to do it starting with the next available
> number, but it doesn't really matter what the number ends up being. This
> column will eventually be an identity column.
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
>|||That's correct. In other words if this database had been done correctly in
the first place, it would have been an identity column. So now I need to
create this identity column based on numbers that already exist, plus
populate those records with new numbers for those records that don't use an
integer value. Once this is done, then I can make the column an identity
column so that future new records are automatically numbered correctly.
Does that make sense?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eBZ%23xThxFHA.1412@.TK2MSFTNGP09.phx.gbl...
> ok...clarify please...IDENTITY is system generated so you don't populate
> it with any values. "any number"?
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||You cannot alter an existing column to be an identity column. You'll have to
create a new table with the identity column and then copy the rows from the
old table into the new one using SET IDENTITY_INSERT ON.
Why are you doing this? Are you planning on using the identity column as the
primary key? Analyze your data more thoroughly - there may be a better
primary key candidate.
To clean up those values try this:
http://milambda.blogspot.com/2005/0...to-integer.html
ML|||No, my primary key is a guid. This column used to be the primary key in an
access database, and was also used for other reasons. Now it's being merged
with the company's sql database. I'm only using the identity feature so it
will automatically provide a number each time a new record is created, which
of course I can do within the program it's being used with also.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:53200E31-72FB-4C15-AF6B-CF6858E3296E@.microsoft.com...
> You cannot alter an existing column to be an identity column. You'll have
> to
> create a new table with the identity column and then copy the rows from
> the
> old table into the new one using SET IDENTITY_INSERT ON.
> Why are you doing this? Are you planning on using the identity column as
> the
> primary key? Analyze your data more thoroughly - there may be a better
> primary key candidate.
> To clean up those values try this:
> http://milambda.blogspot.com/2005/0...to-integer.html
>
> ML|||If the values still need to be unique, then I'd suggest letting the server
assign them.
ML

Converting a binary/hexadecimal to Varchar

How should I convert a binary/hexadecimal value to Varchar
Thanks in Advance,
JakeOriginally posted by Jake K
How should I convert a binary/hexadecimal value to Varchar

Thanks in Advance,
Jake

select cast(Binaryvalue as varchar)sqlsql

Converting 7 (int) to 07 (varchar)

Hi,

I'm converting a query from Access to SQL Server.

In this query I select from a column that contains numbers, the result I want is a varchar that is always 2 chars wide..

Ie:
7 should be selected as '07'
12 should be selected as '12'

In the Access-query it's rather nicely done with:
Format(Str(mycolumn),"00")

I could not find a way to make CONVERT do the same job... but I found that:
LEFT('00',2-LEN(CAST(mycolumn as varchar)))+CAST(mycolumn as varchar)
will do the job.

But it feels like it could be done nicer.. any suggestions?Im Pretty new to this myself but how about

RIGHT('00' + CAST(MyColumn as Varchar(2)),2)

Dave|||Thanks, I was too left oriented in my thinking :-)

Converting 1000 into 10.00

I have been given a Product table whoes all column types are varchar(8000)

One of the column is Price and other is DecimalPosition. Price column includes price without any decimal place and the data in DecimlaPosition column determins where the decimal should be placed.

So for instance, if the Price column includes '1000' and DecimalPosision includes '2' >> then it means that the actual price for this product is '10.00' and NOT '1000'. Similarly, if the DecimalPosision includes '3' >> then it means that the actual price for this product is '1.000' and NOT '1000'
My question is that when I am getting the price for a product from this table, how can I get the price in the correct format, e..g like '10.00' and not '1000'
Should I use SQL statements to convert 1000 into 10.00 or should I use some sort of programming logic to convert 1000 into 10.00.
kind regards

select substring(price,1,decimalPosition) + '.' + substring(price, 1 + decimalPosition, len(price) - decimalPosition)
from YOURTABLE

Nick

Edit: include decimal|||

There's a slight problem in Nick's code. It should be:

select substring(price, 1, len(price) - decimalPosition) + '.' + substring(price, len(price) - decimalPosition + 1, decimalPosition) as col1

However, no matter how you argue, that is just some flawed design. What I would strongly recommend is set up a migration "roll-out" plan for the data that is already in production and convert them to a standard price structure where you drop that decimalPlace column and correct the Price column into an real number.

Cheers,

Justin

ConvertEmptyStringToNull

I have a DB table with a varchar field that doesn't allow nulls, but the SqlDataSource I use to update the row wants to put a null in in exchange of an empty string when the field is empty.

<asp:Parameter Name="comment" ConvertEmptyStringToNull="false" Type="string" /
I even tried settting ConvertEmptyStringToNull to "false" but got nowhere.

I can do this by directly by handling the Updating event of the SqlDataSource, but that's a lot of code. Is there a better way?

I don't know whether this is what you are looking for. Use a space for the DefaultValue.

<UpdateParameters>

<asp:ParameterName="comment"DefaultValue=" "Type="String"/></UpdateParameters>|||

You have to set to ConvertEmptyStringToNull="false" on both the Parameter, and the BoundField control.

For example:

<

asp:BoundFieldDataField="locationName"HeaderText="Location Name"SortExpression="locationName"ConvertEmptyStringToNull="false"/>

<

asp:ParameterName="locationName"Type="String"ConvertEmptyStringToNull="false"/>|||

Hi,

This was a great help, I have been teaching myself ASP.NET and it was the first stumbling block. I was following "SAMS Teach Yourself ASP.NET (24Hrs)" and was on the datagrid view section. It was advising me to set the column value (which was set to not allow nulls) to still be able to except a blank entry. This was supposedly achieved by editing the value via the smart tag and then setting the ConvertEmptyStringToNull, which I did. But still failed with the error "Null expected".

I followed the manual method described above and edited the source code and it worked!

My question is if this is a bug with Visual Studio Express? That is the smart tag function is not updating the Parameter. I had to manually edit the source code in order to get it to work as above. Are there other areas that Visual Studio does not via Design view update the source code side of things?

Regards X

|||

Hi exup,

There are a lot of demos showing how complex web pages can be done without digging into code. The problem is that, unless you want your pages to perform exactly like the demos, you will need to dig into code.

"Teach yourself asp.net in 24 hours" as a book title sets up a somewhat unrealistic expectation. Asp.net is complicated, and needs time to master. I'd give yourself a few months to get comfortable with all of the quirks.

John

Convert/Cast from Varchar to decimal

guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
i had been using a Bulk Insert to a dummy table where all columns are varcha
r.
then selecting that table and running it thru a for each loop in an asp.net
application and attempting the conversion there just using Cdbl(datarow.Item
(0))
or Cdec(datarow.Item(0)).
is there a better way to do this right on the database itself?
because it seems like somewhere an implicit rounding is occuring so i'll get
55.00 where 55.50 should be.
the problem i think is the inconsistant values, but, i thought i'd ask the r
eal experts.
as some of the values in the .csv (formerly .xls) file are 33.02, some are w
hole numbers 234 and even others are 55.5.
ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data t
ype numeric"
if anyone has any suggestions, i'd really appreciate it.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Did you try altering that column in the dummy table to numeric(5, 2) instead
varchar?
AMB
"rik butcher" wrote:

> guys, i've got a quick question on uploading a .txt or .csv file to a tabl
e in sql server 2000.
> i had been using a Bulk Insert to a dummy table where all columns are varc
har.
> then selecting that table and running it thru a for each loop in an asp.ne
t
> application and attempting the conversion there just using Cdbl(datarow.It
em(0))
> or Cdec(datarow.Item(0)).
> is there a better way to do this right on the database itself?
> because it seems like somewhere an implicit rounding is occuring so i'll g
et 55.00 where 55.50 should be.
> the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some ar
e whole numbers 234 and even others are 55.5.
> ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and th
e consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
> if anyone has any suggestions, i'd really appreciate it.
> thanks again
> rik
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Why are all the columns of your dummy table all varchar? Assuming that
the data is clean, you can create the target table with a column of the
desired decimal type and have nothing else to do about conversion.
As far as the error you're seeing with CAST, it indicates that there is
a value in the [Amount] column that represents a value too big for
decimal(5,2), in other words, greater than 999.99 or less than -999.99.
Again assuming that the data is clean, CAST should work if the target
type can hold the values. You'll get a different error if you have non-
numeric strings, like 'abc':
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I don't know the specifications of the Cdbl and Cdec functions, so I won't
comment on why some values seem to be changed more than rounding
should cause.
Steve Kass
Drew University
rbutch@.coair.com wrote:

>guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
>i had been using a Bulk Insert to a dummy table where all columns are varch
ar.
>then selecting that table and running it thru a for each loop in an asp.net
>application and attempting the conversion there just using Cdbl(datarow.Ite
m(0))
>or Cdec(datarow.Item(0)).
>is there a better way to do this right on the database itself?
>because it seems like somewhere an implicit rounding is occuring so i'll ge
t 55.00 where 55.50 should be.
>the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some are
whole numbers 234 and even others are 55.5.
>ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
>if anyone has any suggestions, i'd really appreciate it.
>thanks again
>rik
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
>|||thanks guys. you both were right. using numeric(5,2) worked but i still had
to widen that column again [that's what the overflow message was referring t
o] - so, i dont have to use a dummy table. and bcp bulk insert is working li
ke a charm.
i just had to look thru the actual values and there it was.
sometimes its the simplest things - and i appreciate you guys setting me str
aight on this.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...