473,322 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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

Jun 29 '06 #1
9 58202
At least with SQL 2000 and higher... LEFT & Right functions found in Access appear in SQL server.

Syntax:
LEFT ( character_expression , integer_expression )
RIGHT ( character_expression , integer_expression )
Jun 29 '06 #2
On 29 Jun 2006 11:40:22 -0700, "sql guy123" <st*****@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
Jun 29 '06 #3
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

Jun 29 '06 #4
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/de...ortal_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


Jun 29 '06 #5
On 29 Jun 2006 12:18:57 -0700, "sql guy123" <st*****@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/d...displaylang=en

Roy
Jun 29 '06 #6
What is the definition of dbo.table?

Roy

On 29 Jun 2006 11:40:22 -0700, "sql guy123" <st*****@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'
)

Jun 29 '06 #7
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?


Jun 29 '06 #8
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" <st*****@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?

Jun 30 '06 #9
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" <st*****@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?
Jul 24 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: mitsura | last post by:
Hi, I have included a small listing. The test program opens a panel and show a bitmap. What I want is to when the mouse is over the bitmap panel, I want to trap the left mouse click. The...
9
by: dixie | last post by:
I have been trying to extract the digits between the two / symbols in a date. For example the date (in Australian format, not US) is 23/12/2004. I want to be able to extract the 12 (Month) from...
8
by: A.M | last post by:
Hi, Using C#, what is the equivalent of functions Left, Right and Mid that we had in vb6? Thanks, Alan
4
by: Agnes | last post by:
in .net , any left function ?? or I should use Microsoft.VisualBasic.Left(myString, 5) Thanks
11
by: Bruce A. Julseth | last post by:
I have: If (Microsoft.VisualBasic.Left(TextBox1.Text, 1) = "$") Then TextBox1.Text = Microsoft.VisualBasic.Right(TextBox1.Text, TextBox1.Text.Length - 1) End If Adding: Imports...
6
by: James Brown [MVP] | last post by:
Hi, I am having trouble understanding how the 'const' modifier affects the 'left-right' rule when deciphering c-declarations: const int *x; // x is a pointer to a 'const int' int const *x; ...
8
by: Chaitanya | last post by:
Hello, In my Application i want to know when user clicks both the "Left" and "Right" buttons of the Mouse. I am getting a number like this "3145728". But the MouseButtons Enum contains only Left,...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
0
by: garfieldsevilla | last post by:
I have a report in Access 2003 which consists of a header with two fields and a subform pasted into the centre. When I open this form, it always displays in Print Preview and I cannot find an...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.