Super dumb question… How do you use the ‘like’ function in a BAQ calculated field?
I have a calculated field of type bit (boolean), and want to check if the Part.PartNum field is in a certain format. My format is ss-nnnn where ss is two letters, and nnnn is 4 number characters.
Ignoring the requirement for character types for the moment, I want to check if the PN is like ‘__-____’ (using SQL wildcards in my example)
Any of the following give a syntax error
PartCost.PartNum like '__-____'
(PartCost.PartNum like '__-____' )
(PartCost.PartNum like '__-____' ) = 1
(PartCost.PartNum like '__-____' ) = TRUE
Double clicking the function to insert it, just inserts
While I’d still like to know the syntax for using the like function, i ended up just implementing it as
(case when Len(PartCost.PartNum)=7
AND substring(Upper(PartCost.PartNum), 1, 1)>= 'A' AND substring(Upper(PartCost.PartNum), 1, 1)<= 'Z'
AND substring(Upper(PartCost.PartNum), 2, 1)>= 'A' AND substring(Upper(PartCost.PartNum), 2, 1)<= 'Z'
AND substring(PartCost.PartNum, 3, 1)= '-'
AND substring(PartCost.PartNum, 4, 1)>= '0' AND substring(PartCost.PartNum, 4, 1)<= '9'
AND substring(PartCost.PartNum, 5, 1)>= '0' AND substring(PartCost.PartNum, 5, 1)<= '9'
AND substring(PartCost.PartNum, 6, 1)>= '0' AND substring(PartCost.PartNum, 6, 1)<= '9'
AND substring(PartCost.PartNum, 7, 1)>= '0' AND substring(PartCost.PartNum, 7, 1)<= '9'
then 1 else 0 end)
It wasn’t the pattern I was having problems with, It was the syntax for a calculated field. (even the pattern ‘%’ would give me a syntax error.
Turns out you can’t have an expression that just evaluates to TRUE or FALSE, you have to test for TRUE or FALSE and then return a true or false. But not really ‘true’ or ‘false’, you must return a 1 or 0
This yields an error (PartCost.PartNum like '__-____')
But this works: (case when (PartCost.PartNum like '__-____') then 1 else 0 end)
Case is the SQL equivalent to IF. As for the like function, it is pretty basic.
Like would be either Begins with, Contains or Ends with on a static string.
like ‘05-%’ would list all that began with 05-
like ‘%05-%’ would list all that contains 05-
like ‘%05-’ would list all that end with 05-
there’s also
CASE
WHEN SUBSTRING(PartCost.PartNum, 1, 1) like ‘%[a-z]%’
WHEN SUBSTRING(PartCost.PartNum, 2, 1) like ‘%[a-z]%’
WHEN SUBSTRING(PartCost.PartNum, 3, 1) = ‘-’
WHEN SUBSTRING(PartCost.PartNum, 4, 1) like ‘%[0-9]%’
THEN ‘True’ ELSE ‘False’ END
CASE
WHEN SUBSTRING(PartCost.PartNum, 1, 1) like ‘%[a-z]%’
AND SUBSTRING(PartCost.PartNum, 2, 1) like ‘%[a-z]%’
AND SUBSTRING(PartCost.PartNum, 3, 1) = ‘-’
AND SUBSTRING(PartCost.PartNum, 4, 1) like ‘%[0-9]%’
THEN ‘True’ ELSE ‘False’ END
mess around with this in the sql editor first. Then translate once you get your desired results.
The like expression in BAQ needs “%” not single quotes. In SQL editor it will be single quotes.
select left(PartNum,(charindex('-',PartNum)-1)) as BeforeDash, right(PartNum,(charindex('-',PartNum)-1)) as AfterDash, len(replace(PartNum, '-', '')), len(PartNum)-1 , PartNum
from Part
where
-- one dash only
len(replace(PartNum, '-', '')) = len(PartNum)-1
-- characters before the dash
and left(PartNum,(charindex('-',PartNum)-1)) like '%[a-zA-Z]%'
-- digits only after the dash
and right(PartNum,(charindex('-',PartNum)-1)) like '%[0-9]%'
It wouldn’t always match since % is not a character wildcard itself but only shows when the wild card begins or ends. But you are correct that with dealing with only one character the % sign is unnecessary.
Unfortunately, for Knash’s example if the prefix or suffix is alphanumeric it will show true because it contains not equals.
This will return all items that match the criteria. You can then use the this as a subquery to your main query to show which parts are valid and which are not.
I just validated that only two records are returned.
CREATE TABLE #TempTable (PartNum Varchar(50));
INSERT INTO #TempTable
VALUES
('aa-1234'),
('BB-1235'),
('443-asdf'),
('aq-123-12'),
('erew-123')
select left(PartNum,(charindex('-',PartNum)-1)) as BeforeDash, right(PartNum,(charindex('-',PartNum)-1)) as AfterDash, len(replace(PartNum, '-', '')), len(PartNum)-1 , PartNum
from #TempTable
where
-- one dash only
len(replace(PartNum, '-', '')) = len(PartNum)-1
-- characters before the dash
and left(PartNum,(charindex('-',PartNum)-1)) like '%[a-zA-Z]%'
-- length of left is only two
and len(left(PartNum,(charindex('-',PartNum)-1))) = 2
-- digits only after the dash
and right(PartNum,(charindex('-',PartNum)-1)) like '%[0-9]%'
DROP TABLE #TempTable
This code was not final. The users will still need to tweak as needed. This is getting closer to what is needed. There may still be some situations that need to be fixed. This returns 3 rows.
AA, BB, and er
CREATE TABLE #TempTable (PartNum Varchar(50));
INSERT INTO #TempTable
VALUES
('aa-1234'),
('BB-1235'),
('443-asdf'),
('aq-123-12'),
('er-1223'),
('BC-12f35'),
('B7-1235')
select left(PartNum,(charindex('-',PartNum)-1)) as BeforeDash,
SUBSTRING(PartNum,CHARINDEX('-',PartNum)+1,LEN(PartNum)) as AfterDash,
len(replace(PartNum, '-', '')),
len(PartNum)-1 ,
PartNum
from #TempTable
where
-- one dash only
len(replace(PartNum, '-', '')) = len(PartNum)-1
-- characters before the dash
and left(PartNum,(charindex('-',PartNum)-1)) like '[a-zA-Z][a-zA-Z]'
-- length of left is only two
and len(left(PartNum,(charindex('-',PartNum)-1))) = 2
-- digits only after the dash
and ISNUMERIC(SUBSTRING(PartNum,CHARINDEX('-',PartNum)+1,LEN(PartNum))) = 1
and len(SUBSTRING(PartNum,CHARINDEX('-',PartNum)+1,LEN(PartNum))) = 4
DROP TABLE #TempTable
I know the original question was about like ‘%%’ which does have to equal a true/false which was answered but… this might be the simplest solution for your calculated field?
CASE WHEN PartCost.PartNum like ‘[a-zA-Z][a-zA-Z]-[0-9][0-9][0-9][0-9]’ and LEN(PartCost.PartNum) = 7 THEN 1 ELSE 0 END
