Left and Instr function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Left and Instr function

Author

Topic

vgunas1
Starting Member

18 Posts

Posted - 2003-05-16 : 09:59:31

I am using the left and the Instr function in one of my queries. The problem is that the field that I am using in the query has spaces. For eg.

SELECT (Left(First Last,InStr(First Last,"/")-1)) AS Expr1
FROM Table1;

I cannot name the field FirstLast or First_Last because I am importing it from an excel using a macro which has the field as First Last.
The field First Last has data which looks like Louisa Alcott/IT/USA/America.

I need to get just Louisa Alcott using the query. Can anybody help me with this?

Thanks!!!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-16 : 10:19:58

SELECT (Left([First Last],InStr([First Last],"/")-1)) AS Expr1
FROM Table1;

(note the [])

Replace "Expr1" with whatever you want the resulting field name to be, by the way. e.g.,:

SELECT (Left([First Last],InStr([First Last],"/")-1)) AS NameOnly
FROM Table1;

- Jeff

vgunas1
Starting Member

18 Posts

Posted - 2003-05-16 : 10:23:01

Thanks a lot Jeff!! That really helped!

vgunas1
Starting Member

18 Posts

Posted - 2003-05-16 : 11:44:01

Another question similar to the previous one:

If my field First Last by itself has no / then the query gives me #error. Does anyone know as to how I can eliminate this? If the field has no / or is empty I want the result of the query to be the same as First Last.

For eg.

If First Last is Louisa Alcott then I want the query to return Louisa Alcott. If it is Louisa Alcott/IT/US I want the query to give me Louisa Alcott.

Please advise!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 11:24:59


SELECT 
IIf(InStr(1,[First Last],"/")=0,
   [First Last],Left([First Last],
   InStr(1,[First Last],"/")-1)) AS Expr1
FROM Table1;

Edited by - ValterBorges on 05/17/2003 11:25:35

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-17 : 11:45:56

vgunas1 --

I guess you DIDN'T get the IIF() function to work after all?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26289

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 11:55:44

I saw the cross post but it was too late.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources