| Author |
Message |
Laguna
Newbie
Joined: 20 Sep 2012
Online Status: Offline
Posts: 17
|
   Topic: extract or split a field
Posted: 06 Feb 2013 at 10:46am |
Hello, I have a SQL command concatenating two fields to be displayed as... {field1} - {field2}. For example materialname - materialtype.
The length of both fields vary. I need to extract out field1 and use it in my select statement to filter data. Likewise with field2.
How do I pull these fields out?
Thanks
Edited by Laguna - 06 Feb 2013 at 10:48am
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Feb 2013 at 10:57am |
|
why not use a like or instr() in your select statment instead.
|
|
IP Logged |
|
Laguna
Newbie
Joined: 20 Sep 2012
Online Status: Offline
Posts: 17
|
Posted: 06 Feb 2013 at 11:05am |
The report parameters are dynamic, and our policy here is not to use 'like' in our code. The admins would reject the report due to strain on the database.
I'm new, i'll look into using instr()
|
|
IP Logged |
|
Laguna
Newbie
Joined: 20 Sep 2012
Online Status: Offline
Posts: 17
|
Posted: 06 Feb 2013 at 11:10am |
I use InStr()... InStr ({?PSampleType}, " - ")
and I get an error saying A string is required here {?PSampleType} where my parameter is.
|
|
IP Logged |
|
Schugs
Newbie
Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
|
Posted: 06 Feb 2013 at 11:13am |
|
how bout left({fullname},instr({fullname}," - ")-1)
|
|
IP Logged |
|
Schugs
Newbie
Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
|
Posted: 06 Feb 2013 at 11:15am |
|
InStr() will return an integer. Use that integer to find the length of the material name*
Edited by Schugs - 06 Feb 2013 at 11:16am
|
|
IP Logged |
|
Schugs
Newbie
Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
|
Posted: 06 Feb 2013 at 11:18am |
|
Can you give us some example data? what is the {?PSampleType} prompting? a drop down, a field the user types in?
|
|
IP Logged |
|
Laguna
Newbie
Joined: 20 Sep 2012
Online Status: Offline
Posts: 17
|
Posted: 06 Feb 2013 at 11:18am |
I hope I understand what you're telling me. If I state...
left({?PSampleType},instr({?PSampleType}," - ")-1)
I still get an error stating a string is required instr({?PSampleType} here
|
|
IP Logged |
|
Schugs
Newbie
Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
|
Posted: 06 Feb 2013 at 11:22am |
|
{?PSampleType} is a prompt from the user correct? are you wanting someone to be able to search for "Wood - Plywood" by typing "Wood" or are you wanting to generate a drop down that has "Wood" and "steel" from a table that has "Wood - Plywood" and "Steel - Stainless" as records?
|
|
IP Logged |
|
Laguna
Newbie
Joined: 20 Sep 2012
Online Status: Offline
Posts: 17
|
Posted: 06 Feb 2013 at 11:32am |
{?PSampleType} is a dynamic parameter. The list of values it returns is created by concatenating materialname with materialtype. These sample types are displayed in the list as
Wood - Plywood
Steel - Stainless.
Now I need to define the selected materialnames to show in my report, like Wood and Steel, but not Plastic. As well as materialtypes.
more...
so my parameter {?PSampleType} has Wood - Plywood for a certain record. Now I need to pull out Wood and Plywood separately from {?PSampleType} so I can restricted certain materialnames and materialtypes to show.
Edited by Laguna - 06 Feb 2013 at 11:41am
|
|
IP Logged |
|
|
|
