• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • Last Modified:

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.
0
rwheeler23
Asked:
rwheeler23
  • 3
  • 2
1 Solution
 
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now