left() and right() function in MS SQL vs MS ACCESS

[sql guy123]

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!!!

[skirack]

At least with SQL 2000 and higher... LEFT & Right functions found in Access appear in SQL server.

Syntax:
LEFT ( character_expre ssion , integer_express ion )
RIGHT ( character_expre ssion , integer_express ion )

[Roy Harvey]

Re: left() and right() function in MS SQL vs MS ACCESS

On 29 Jun 2006 11:40:22 -0700, "sql guy123" <stoppal@hotmai l.com>
wrote:
[color=blue]
>Select field1, field2 FROM table1 Where left(field1,4)= left(field2,4)
>
>(MS SQL does not have left() and right() functions)[/color]

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_expre ssion , integer_express ion )

Arguments
character_expre ssion

Is an expression of character or binary data. character_expre ssion can
be a constant, variable, or column. character_expre ssion 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_expre ssion.

integer_express ion

Is a positive integer that specifies how many characters of the
character_expre ssion will be returned. If integer_express ion 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_expre ssion , integer_express ion )

Arguments
character_expre ssion

Is an expression of character or binary data. character_expre ssion can
be a constant, variable, or column. character_expre ssion 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_expre ssion.

integer_express ion

Is a positive integer that specifies how many characters of the
character_expre ssion will be returned. If integer_express ion is
negative, an error is returned.

Return Types
varchar or nvarchar.

Roy Harvey
Beacon Falls, CT

[sql guy123]

Re: left() and right() function in MS SQL vs MS ACCESS

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

[SQL Menace]

Re: left() and right() function in MS SQL vs MS ACCESS

For SQL server 2005

BOL = Books On Line
(http://msdn2.microsoft.com/en-us/library/ms130214.aspx)

or Start-->programs-->Microsoft SQL Server 2005-->Documentatio n 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:[color=blue]
> 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[/color]

[Roy Harvey]

Re: left() and right() function in MS SQL vs MS ACCESS

On 29 Jun 2006 12:18:57 -0700, "sql guy123" <stoppal@hotmai l.com>
wrote:
[color=blue]
>Also can I have the
>link to the online book you referred to?[/color]

You can download the SQL Server 2000 docs:

http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en

Roy

[Roy Harvey]

Re: left() and right() function in MS SQL vs MS ACCESS

What is the definition of dbo.table?

Roy

On 29 Jun 2006 11:40:22 -0700, "sql guy123" <stoppal@hotmai l.com>
wrote:
[color=blue]
>
>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'
> )[/color]

[sql guy123]

Re: left() and right() function in MS SQL vs MS ACCESS

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:[color=blue]
> What is the definition of dbo.table?
>[/color]

[Roy Harvey]

Re: left() and right() function in MS SQL vs MS ACCESS

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@hotmai l.com>
wrote:
[color=blue]
>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:[color=green]
>> What is the definition of dbo.table?[/color][/color]

[sql guy123]

Re: left() and right() function in MS SQL vs MS ACCESS

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@hotmai l.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?