Create SQL function to return TRUE/FALSE based on substring search of table

How to creation a SQL function that will return a TRUE/FALSE value based on a list of values that are substrings of other values.

I need to take a part number and then compare this part number, which is defined as CHAR(20) and compare it to a string of other part numbers and if a match is found return TRUE otherwise return FALSE. In this list are what are known as base part numbers. So, for example, the complete part number sent down will be 123456P023, so when I search this table it will find 123456 and return TRUE.  How do I structure this function to proceed through each record and perform the comparison and return TRUE or FALSE?

CREATE TABLE #TEMPPARTS (SUBPART CHAR(20))
INSERT INTO #TEMPPARTS (SUBPART)
      VALUES ('123456')
INSERT INTO #TEMPPARTS (SUBPART)
      VALUES ('U67')
INSERT INTO #TEMPPARTS (SUBPART)
      VALUES ('98765')
INSERT INTO #TEMPPARTS (SUBPART)
      VALUES ('A100101')

This has all come about because any one base part number could have hundreds of complete part numbers.  We do ot want to populate a table with every possible combination.
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NerdsOfTechTechnology ScientistCommented:
Use UNION to eliminate duplicates
 SELECT 'TRUE' AS TF, SUBPART
 FROM TEMPPARTS
 WHERE SUBPART LIKE '%123456%'
 UNION
 SELECT 'FALSE' AS TF, SUBPART
 FROM TEMPPARTS
 WHERE SUBPART NOT LIKE '%123456%'

Open in new window


http://sqlfiddle.com/#!18/fcf67/20
0
rwheeler23Author Commented:
I need to read all the records in the TEMPPARTS table and taking the value of SUBPART substitute that into the LIKE portion of the query. The WHERE clause needs to become dynamic. So in this case it becomes

 SELECT SUBPART
 FROM TEMPPARTS
 WHERE @MyPartNumber  LIKE '%123456%'

 SELECT SUBPART
 FROM TEMPPARTS
 WHERE @MyPartNumber  LIKE '%U67%'

 SELECT SUBPART
 FROM TEMPPARTS
 WHERE @MyPartNumber  LIKE '%98765%'

 SELECT SUBPART
 FROM TEMPPARTS
 WHERE @MyPartNumber  LIKE '%A100101%'

So the full part number value sent down 123456P023 (@MyPartNumber)so the first one will qualify as the statement becomes

 SELECT SUBPART
 FROM TEMPPARTS
 WHERE @MyPartNumber LIKE '%123456%'

will be true.

The end result is a function that would be called using SELECT My_Function('123456P023') and I need this function to return a True or False value.
0
NerdsOfTechTechnology ScientistCommented:
 DECLARE @MyPartNumber CHAR(20)
 SET @MyPartNumber = '123456P023'
 SELECT 
	 @MyPartNumber AS PARTNUM, '%123456%' AS S, (CASE WHEN @MyPartNumber LIKE '%123456%' THEN 'TRUE' ELSE 'FALSE' END) AS TF
 UNION ALL
 SELECT 
	@MyPartNumber AS PARTNUM, '%U67%' AS S, (CASE WHEN @MyPartNumber LIKE '%U67%' THEN 'TRUE' ELSE 'FALSE' END) AS TF
 UNION ALL
 SELECT 
	@MyPartNumber AS PARTNUM, '%98765%' AS S, (CASE WHEN @MyPartNumber LIKE '%98765%' THEN 'TRUE' ELSE 'FALSE' END) AS TF
 UNION ALL
 SELECT 
	@MyPartNumber AS PARTNUM, '%A100101%' AS S, (CASE WHEN @MyPartNumber LIKE '%A100101%' THEN 'TRUE' ELSE 'FALSE' END) AS TF

Open in new window


http://sqlfiddle.com/#!18/bd5b9/17



To return the top row:
 DECLARE @MyPartNumber CHAR(20)
 SET @MyPartNumber = '123456P023'
 SELECT TOP 1 * FROM 
 (
 SELECT 
	 @MyPartNumber AS PARTNUM, '%123456%' AS S, (CASE WHEN @MyPartNumber LIKE '%123456%' THEN 'TRUE' ELSE 'FALSE' END) AS TF
 UNION ALL
 SELECT 
	@MyPartNumber AS PARTNUM, '%U67%' AS S, (CASE WHEN @MyPartNumber LIKE '%U67%' THEN 'TRUE' ELSE 'FALSE' END) AS TF
 UNION ALL
 SELECT 
	@MyPartNumber AS PARTNUM, '%98765%' AS S, (CASE WHEN @MyPartNumber LIKE '%98765%' THEN 'TRUE' ELSE 'FALSE' END) AS TF
 UNION ALL
 SELECT 
	@MyPartNumber AS PARTNUM, '%A100101%' AS S, (CASE WHEN @MyPartNumber LIKE '%A100101%' THEN 'TRUE' ELSE 'FALSE' END) AS TF
 ) S1 
 WHERE S1.TF = 'TRUE'
 ORDER BY S1.S

Open in new window

http://sqlfiddle.com/#!18/bd5b9/20
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

rwheeler23Author Commented:
After several iterations I finally got this to work.

CREATE FUNCTION [dbo].[fn_Sub_Alert]
(
@SearchCriteria char(51)
)
RETURNS BIT

WITH SCHEMABINDING AS

BEGIN
DECLARE @ANSWER AS BIT

IF CHARINDEX('%', @SearchCriteria) = 0

SET @SearchCriteria = '%' + @SearchCriteria + '%'

SELECT @ANSWER =
          CASE WHEN EXISTS (
            SELECT PARTNUMB
            FROM [dbo].ALRTNOTE
            WHERE @SearchCriteria LIKE '%' + LTRIM(RTRIM(PARTNUMB)) + '%' )
      THEN CAST(1 AS BIT)
      ELSE CAST(0 AS BIT) END

RETURN @ANSWER

END
      
GO
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xiao jinshouCommented:
I think function probably not a good solution. I have my query below:

----------------------------------------------------------------------------------

create table #TBL (Col Varchar(20))

insert into #TBL values
         ('a123456')
            ,('b1234')
            ,('c12')

declare @searchstr Varchar(20) = '123'

 SELECT distinct
  Col
 ,Case
   When Col like '%' + @searchstr + '%' Then 'TRUE'
   ELSE 'FALSE'
 End As Col1
 FROM #TBL

 drop table #TBL
0
rwheeler23Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.