Solved How can I use 2 left function together? (1 Viewer)

RayJ

New member
Local time
Today, 03:57
Joined
Apr 6, 2022
Messages
9
I am working with a table that has a field name System where the values are like this:

DNA-Data
Oracle_DIMDW

I only need to capture the values just before the "-" and the "_". What i am expecting is "DNA" and "Oracle".
I have managed to capture the string "DNA" using the code below:

Code:
System_Name: Left([System],(InStr(1,[System],"-"))-1)

How can I include another Left function within the same code to capture "_" or "Oracle"?


Thanks in advance
Ray
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:57
Joined
Oct 29, 2018
Messages
21,358
Hi Ray. Welcome to AWF!

Can you post some sample data to make sure we get the right expression? Thanks.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:57
Joined
Sep 21, 2011
Messages
14,050
Check for one or the other and use expression accordingly?
Assuming there will always be one there?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:57
Joined
Feb 19, 2013
Messages
16,553
What is the context of the requirement? To display or for use in a criteria? If the latter you can use like dna*
 

RayJ

New member
Local time
Today, 03:57
Joined
Apr 6, 2022
Messages
9
Hi Ray. Welcome to AWF!

Can you post some sample data to make sure we get the right expression? Thanks.
Those are the only 2 different types of string under the system field.

SYSTEM (field name)
------------------
DNA-Data
Oracle_DIMDW
 

RayJ

New member
Local time
Today, 03:57
Joined
Apr 6, 2022
Messages
9
What is the context of the requirement? To display or for use in a criteria? If the latter you can use like dna*
I am sorry. I should've mentioned it. I am looking to use this expression in a query for viewing purpose.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:57
Joined
Oct 29, 2018
Messages
21,358
Those are the only 2 different types of string under the system field.

SYSTEM (field name)
------------------
DNA-Data
Oracle_DIMDW
I guess I should have added showing a before and after data set would be nice. So, you're saying you want the query to drop everything after the "-" or "_", is that correct?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:57
Joined
Oct 29, 2018
Messages
21,358
I am sorry. I should've mentioned it. I am looking to use this expression in a query for viewing purpose.
I am thinking it might be easier to simply try something like:
Code:
Switch(InStr(ColumnName, "DNA-")>0, "DNA", InStr(ColumnName, "Oracle_")>0, "Oracle")
 

RayJ

New member
Local time
Today, 03:57
Joined
Apr 6, 2022
Messages
9
I am thinking it might be easier to simply try something like:
Code:
Switch(InStr(ColumnName, "DNA-")>0, "DNA", InStr(ColumnName, "Oracle_")>0, "Oracle")

Using your expression above , for some reason it is only extracting the word Oracle and coming up blank for DNA. :/

I have got the following result below using : System_Name: Left([System],(InStr(1,[System],"-"))-2)
But as you can see, that expression is throwing an error "#Func!" for Oracle..




Left Function Query1.JPG
 
Last edited:

RayJ

New member
Local time
Today, 03:57
Joined
Apr 6, 2022
Messages
9
Using your expression above , for some reason it is only extracting the word Oracle and coming up blank for DNA. :/

I have got the following result below using : System_Name: Left([System],(InStr(1,[System],"-"))-2)
But as you can see, that expression is throwing an error "#Func!" for Oracle..


View attachment 99772

View attachment 99773

I am sorry... silly me.. I was missing the space between DNA and "-" . Got it! Its good now.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:57
Joined
Oct 29, 2018
Messages
21,358
Using your expression above , for some reason it is only extracting the word Oracle and coming up blank for DNA. :/

I have got the following result below using : System_Name: Left([System],(InStr(1,[System],"-"))-2)
But as you can see, that expression is throwing an error "#Func!" for Oracle..


View attachment 99772

View attachment 99773
Hi. My expression wasn't returning "DNA" because your actual data seems to have a space between the system name and the dash.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:57
Joined
Oct 29, 2018
Messages
21,358
I am sorry... silly me.. I was missing the space between DNA and "-" . Got it! Its good now.
LOL. Our posts got crossed. Glad to hear you got it sorted out. Good luck with your project.
 

RayJ

New member
Local time
Today, 03:57
Joined
Apr 6, 2022
Messages
9
LOL. Our posts got crossed. Glad to hear you got it sorted out. Good luck with your project.
haha! Thanks for your help!

I do have another question though. Just thinking out loud and wondering what if in the near future more systems get added to the list which may not have any special character or even different ones like (".")
What would be the best approach to tackle that.

So like, what if the list gets bigger and I get something like this:

System
---------------
DNA - Open
Oracle_Bank
AWS
BI.XLS
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:57
Joined
Oct 29, 2018
Messages
21,358
haha! Thanks for your help!

I do have another question though. Just thinking out loud and wondering what if in the near future more systems get added to the list which may not have any special character or even different ones like (".")
What would be the best approach to tackle that.

So like, what if the list gets bigger and I get something like this:

System
---------------
DNA - Open
Oracle_Bank
AWS
BI.XLS
In that case, I would suggest you create a systems table and then join it to your other table in your query using a non-equi join (e.g. Like).
 

RayJ

New member
Local time
Today, 03:57
Joined
Apr 6, 2022
Messages
9
In that case, I would suggest you create a systems table and then join it to your other table in your query using a non-equi join (e.g. Like).
Ya, thats what i was thinking as well. I may have to maintain that list manually as new systems gets added. Thanks again! :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:57
Joined
Oct 29, 2018
Messages
21,358
Ya, thats what i was thinking as well. I may have to maintain that list manually as new systems gets added. Thanks again! :)
We're all happy to assist. Cheers!
 

Users who are viewing this thread

Top Bottom