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

rwheeler23
rwheeler23 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NerdsOfTechTechnology Scientist

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

Author

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

Commented:
 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
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

President
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
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
rwheeler23President

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial