I need to remove everything after a character in the partnum. I know how to do this in Excel, but I need to do it in the BAQ. The trim doesn’t seem to work in the baq for what I am trying to do.
You could use a combination of left and charindex
1 Like
Can you show me an example? Not used charindex.
the commands you want are the indexof function and the left command
try this:
//first setup some variables for demo purposes
string myPart = "abcd-1234";
string prefix= myPart; //in case there is no separator
string separator = "-";
int separatorLocation = myPart.IndexOf(separator);
if (separatorLocation >=0) {
prefix = myPart.Left(separatorLocation);
}
1 Like
similar to what @timshuwy said… although I thought you needed it in a calculated field in a BAQ. For a calculated field in BAQ:
left(Part.PartNum, charindex('-', Part.PartNum))
The above would return the first X characters of PartNum where X is the position of the hyphen/dash character. If there is no hyphen/dash, it should return the entire partnumber. Replace the hyphen/dash with whatever character you’re looking for. Also replace the table you’re looking in if it’s not Part.
3 Likes
Oops… missed the “BAQ” requirement. 
2 Likes
Are you sure that if the searched character isn’t found that it will return the whole searched in string?
LEFT("Hello World", charindex("-","Hello World"))
Wouldn’t the charindex("-","Hello World") part return a 0, and the the end result would be an empty string?
Edit
A test of LEFT('Hello',0) yields an empty string
2 Likes
Thank you! I was able to get it to work. My character was a “.” and the end result still sows the “.”. Is there a way to remove the “.”? If not, I can work with it, I was just curious.
charIndex('.',Part.PartNum)-1
Which will throw an erro if the searched item isn’t found. So try:
iif(charindex('.',Part.PartNum) < 2,Part.PartNum,Left(Part.PartNum, charindex('.',Part.PartNum)-1))
edit
correction of adding the -1 for stripping the search character.
1 Like
I was mistaken. I was confusing the left function with the charindex.
I believe you’re right.
I never get tired of hearing that.
2 Likes
