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?