Solved How can I use 2 left function together?

[RayJ]

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:

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]

Hi Ray. Welcome to AWF!

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

 

[Gasman]

Check for one or the other and use expression accordingly?
Assuming there will always be one there?

 

[CJ_London]

What is the context of the requirement? To display or for use in a criteria? If the latter you can use like dna*

 

[RayJ]

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]

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]

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

Switch(InStr(ColumnName, "DNA-")>0, "DNA", InStr(ColumnName, "Oracle_")>0, "Oracle")

 

[RayJ]

I am thinking it might be easier to simply try something like:

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

 

[RayJ]

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]

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

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]

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]

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]

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!