I normally use MS ACCESS vs MS SQL,, which has a left() and right() function. I need to use MS SQL for this project but I am not familiar with it. I have read a few books, but can not figure out how to do this. Please help. If I need to compare the first 4 letters of a field, with the first four letters of another field, how can I do this? Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4) (MS SQL does not have left() and right() functions) Please help. In addition, I have a CSV file with data like 10.20, which I import inrto a numberic field. Unforunately the value gets changed to 10. It's seems to get rounded. How can I fix this. The import SQL I use is.... BULK INSERT dbo.table FROM 'c:\MYDATA.CSV WITH ( FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) Thank you in advance!!!
On 29 Jun 2006 11:40:22 -0700, "sql guy123" <stoppal@hotmail.com> wrote: >Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4) > >(MS SQL does not have left() and right() functions) MS SQL Server does have both LEFT and RIGHT functions. From the Books on Line for SQL Server 2000: RIGHT Returns the right part of a character string with the specified number of characters. Syntax RIGHT ( character_expression , integer_expression ) Arguments character_expression Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type (except text or ntext) that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression. integer_expression Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. Return Types varchar or nvarchar LEFT Returns the left part of a character string with the specified number of characters. Syntax LEFT ( character_expression , integer_expression ) Arguments character_expression Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type (except text or ntext) that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression. integer_expression Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. Return Types varchar or nvarchar. Roy Harvey Beacon Falls, CT
your right I was missing a comma. My mistake. Also can I have the link to the online book you referred to? Any idea on the import issue? thanks
For SQL server 2005 BOL = Books On Line (http://msdn2.microsoft.com/en-us/library/ms130214.aspx) or Start-->programs-->Microsoft SQL Server 2005-->Documentation and Tutorials-->SQL Server Books On Line For SQL server 2000 Start-->programs-->Microsoft SQL Server--> Books On Line or http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp Denis the SQL Menace http://sqlservercode.blogspot.com/ sql guy123 wrote: > your right I was missing a comma. My mistake. Also can I have the > link to the online book you referred to? > > Any idea on the import issue? > > thanks
On 29 Jun 2006 12:18:57 -0700, "sql guy123" <stoppal@hotmail.com> wrote: >Also can I have the >link to the online book you referred to? You can download the SQL Server 2000 docs: http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en Roy
What is the definition of dbo.table? Roy On 29 Jun 2006 11:40:22 -0700, "sql guy123" <stoppal@hotmail.com> wrote: > >In addition, I have a CSV file with data like 10.20, which I import >inrto a numberic field. Unforunately the value gets changed to 10. >It's seems to get rounded. How can I fix this. >The import SQL I use is.... > >BULK INSERT dbo.table > FROM 'c:\MYDATA.CSV > WITH > ( > FIRSTROW = 1, > FIELDTERMINATOR = ',', > ROWTERMINATOR = '\n' > )
I'm not sure if I understand what you mean by definition. It's a table in my database. Does that answer your question, or do you need more?\ thanks Roy Harvey wrote: > What is the definition of dbo.table? >
CREATE TABLE, so that we know the data types of the columns. You described a problem when importing data into a "numberic field". The precise data type of the column is rather important. Roy Harvey Beacon Falls, CT On 29 Jun 2006 16:45:48 -0700, "sql guy123" <stoppal@hotmail.com> wrote: >I'm not sure if I understand what you mean by definition. It's a table >in my database. Does that answer your question, or do you need more?\ > >thanks > > > >Roy Harvey wrote: >> What is the definition of dbo.table?
thanks Roy Harvey wrote: > CREATE TABLE, so that we know the data types of the columns. You > described a problem when importing data into a "numberic field". The > precise data type of the column is rather important. > > Roy Harvey > Beacon Falls, CT > > On 29 Jun 2006 16:45:48 -0700, "sql guy123" <stoppal@hotmail.com> > wrote: > > >I'm not sure if I understand what you mean by definition. It's a table > >in my database. Does that answer your question, or do you need more?\ > > > >thanks > > > > > > > >Roy Harvey wrote: > >> What is the definition of dbo.table?