convert ###,###,##0 to INT

Hi,

I've imported an Excel file into a work table, via an Access Project. One of my fields is an integer, represented in Excel with thousands separator e.g. 3,137,458

The above now sits in a varchar column, and I need to convert these values to an INT. Strangely, is numeric() returns One, but then convert( int, ...) does not like the commas.

To add insult to injury, my MSDE does not seem to allow me to CREATE FUNCTION. It protests even if I do Grant Create Function to Login, while running as 'sa'. Side question: is this a known limitation of MSDE ?

Is there an efficient way to convert such strings to Int ?

I note that the commas may actually be missing, since their presence depends on the "Digit Grouping" value in the Regional Settings of Control Panel.

In the past, I was using Sybase, and I had to use set-based queries, running against a few work fields in my table. The first query would use charindex() to find the position of the first comma, if any. The second query would pick up the portion of the string up to the comma, then another query chasing the next comma, etc. Rather painful.Hmm...maybe you could try playing around with the replace command to filter out the commas.

I tested this 1 line code in QA and it works fine.

select cast(replace('3,137,458',',','') as int).|||oops, temporary blindness ... apologies ... please ignore this question

convert( int, REPLACE( column_name, ',', '' ) )|||thanks, mate, I've just found it at the same time. Works like a charm.
Me self-learner too...

Conversion from type SqlInt32 to type Integer is not valid]

Hi all,

I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine.

The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings.

Any suggestions.

Best Regards

Wafi Mohtaseb

You can perform an explicit conversion between those two types

Dim xAs System.Data.SqlTypes.SqlInt32 = 5Dim yAs Integer y =CType(x,Integer)

|||

This is what I am doing in the code, and its working on my local server, the problem appears when I uploaded the site to the hosting company server.

Best regards,

|||

please post the code you are having trouble with.

|||

1Public Overrides Function Insert()As Boolean2 Dim cmdToExecuteAs SqlCommand =New SqlCommand3 cmdToExecute.CommandText ="dbo.[UserFiles_Insert]"4 cmdToExecute.CommandType = CommandType.StoredProcedure56' // Use base class' connection object7 cmdToExecute.Connection = _mainConnection89Try10 cmdToExecute.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int, 4, ParameterDirection.Input,False, 10, 0,"", DataRowVersion.Proposed, _userID))11 cmdToExecute.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileName))12 cmdToExecute.Parameters.Add(New SqlParameter("@.FileType", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileType))13 cmdToExecute.Parameters.Add(New SqlParameter("@.FileSize", SqlDbType.Decimal, 9, ParameterDirection.Input,False, 18, 0,"", DataRowVersion.Proposed, _fileSize))14Dim lengthAs Integer = 015If Not _fileContent.IsNullThen16 length = _fileContent.Length17End If18 cmdToExecute.Parameters.Add(New SqlParameter("@.FileContent", SqlDbType.Image, length, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileContent))19 cmdToExecute.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, 4, ParameterDirection.Output,False, 10, 0,"", DataRowVersion.Proposed, _fileID))2021' // Open connection.22 _mainConnection.Open()2324' // Execute query.25 cmdToExecute.ExecuteNonQuery()26Dim _fileIDAs SqlInt32

//Error ocures here27_fileID =New SqlInt32(CType(cmdToExecute.Parameters.Item("@.FileID").Value,Integer))28Return True29 Catch exAs Exception30' // some error occured. Bubble it to caller and encapsulate Exception object31Throw New Exception("UserFiles::Insert::Error occured.", ex)32Finally33' // Close connection.34 _mainConnection.Close()35 cmdToExecute.Dispose()36End Try37 End Function

contains keyword for integer field type

I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.

Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.
Something like a CONTAINS keyword...

If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.

try something like this:

select

ID/10 firstDigit,
ID- ID/10*10 secondDigit
from(select 37 [ID])aa

It will work as long as Your ID is Int type if it is not int do this

select ID/10 firstDigit,
ID- ID/10*10 secondDigit
from(select cast(37.00 as int) [ID])aa

You can also use substring
selectsubstring(ID,1,1) first,substring(ID,2,1) second
from(selectcast(37asvarchar(20)) [ID])aa

Good luck